![]() I am making no assertions about this other than that this is a really ugly but effective way to get how many rows exist in each table in the database regardless of table engine and without having to have permission to install stored procedures, and without needing to install ruby or php. # rowpicker DBUSER DBPASS DBNAME įor t in `mysql -u "$UN" -p"$PW" "$DB" -e "SHOW TABLES $PAT"` doĬ=`mysql -u "$UN" -p"$PW" "$DB" -e "SELECT count(*) FROM $t"` This is how I pick my potatoes: # Put this function in your bash and call with: With InnoDB, I only know one way, which is to count. Poster wanted row counts without counting, but didn't specify which table engine. ![]() This may be an issue if you have no tables with auto increment, though. The beauty of this is that the row counts returned in performance_schema are erased for you, as well, because greatest does not work on nulls. ![]() GREATEST(SUM(TABLE_ROWS), SUM(AUTO_INCREMENT)) AS DBRowsĪuto Increment will always be +1 * (table count) rows off, but even with 4,000 tables and 3 million rows, that's 99.9% accurate. You could then easily use PHP or whatever to return the max of the 2 data columns to give the "best estimate" for row count. | information_schema | 163840 | NULL | NULL | | Database | DBSize | DBRows | DBAutoIncCount | SUM(data_length + index_length) AS 'DBSize', There's a bit of a hack/workaround to this estimate problem.Īuto_Increment - for some reason this returns a much more accurate row count for your database if you have auto increment set up on tables.įound this when exploring why show table info did not match up with the actual data. SELECT SUM(RECORD_COUNT) AS TOTAL_DATABASE_RECORD_CT FROM TCOUNTS SET = CONCAT("INSERT INTO TCOUNTS(SELECT '", TNAME, "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")") SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() ĭECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 To run it after adding this procedure: CALL `COUNT_ALL_RECORDS_BY_TABLE` () ĬREATE PROCEDURE `COUNT_ALL_RECORDS_BY_TABLE`() This stored procedure lists tables, counts records, and produces a total number of records at the end. SELECT "ndb_binlog_index" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.ndb_binlog_index UNIONĬopy and paste except for the last UNION to get nice output like, +-+-+ SELECT "host" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.host UNION SELECT "help_topic" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_topic UNION SELECT "help_relation" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_relation UNION SELECT "help_keyword" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_keyword UNION SELECT "help_category" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_category UNION SELECT "general_log" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.general_log UNION It produces output like this: SELECT "func" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.func UNION '" AS table_name, COUNT(*) AS exact_row_count FROM `', Here's a relatively hacky (but flexible/adaptable) way of generating a big SQL statement you can paste into a new query, without installing Ruby gems and stuff. ![]() So the estimates should be good unless you had major changes very recently.Like and others I found INFORMATION_SCHEMA.TABLES unreliable (using InnoDB, MySQL 5.1.44), giving different row counts each time I run it even on quiesced tables. If you are running the autovacuum daemon as is the default for modern PostgreSQL, ANALYZE is run automatically, too (except for temporary tables which need manual attention). If you didn't ANALYZE recently (after last changes), the estimates will be off more or less. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. This is only an estimate used by the planner. Quoting the manual for Postgres 13 on pg_class.reltuples: Number of live rows in the table. The cast to bigint formats the real number nicely, especially for big counts. SELECT reltuples :: bigint AS estimate FROM pg_class WHERE oid = 'schema_name.table_name' :: regclass
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |