… at information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
…accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index …SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz.TABLE_SCHEMA…
Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine
May 24, 2008 By Leave a Comment
…INFORMATION_SCHEMA tables that show some status information about InnoDB. Here are the tables: mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE ‘INNODB%’; +—————————————-+ | Tables_in_INFORMATION_SCHEMA…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
…/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL This customer was… simply use the INFORMATION_SCHEMA.INNODB_SYS_INDEXES table, which exists in Percona Server 5.1, but doesn’t appear in MySQL until 5… Server or MysqL 5.6. Another person (I’m looking at you, Baron) was adverse to trusting INNODB_SYS_INDEXES from some…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…_SYS_INDEXES Percona Server has some extra tables in INFORMATION_SCHEMA that can help us to find the different indexes and types. mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… server is not heavily loaded, I may even do some INFORMATION_SCHEMA queries to help me find the biggest tables and so… exist but have been dropped from InnoDB, for example. (The INFORMATION_SCHEMA query will surely trigger errors about this.) There are a… checking for default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on. As with…
Post: Researching your MySQL table sizes
March 17, 2008 By 26 Comments
…, rows, total data in index size for given MySQL Instance SELECT count(*) tables, concat(… information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; +——–+——————–+——-+——-+——-+————+———+ | tables | table_schema …
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
…index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema…
Post: Google's user_statistics V2 port and changes
September 12, 2008 By 9 Comments
… EMPTY_QUERIES: 70748 and INDEX/TABLE statistics: mysql> select * from information_schema.index_STATISTICS limit 10; +————–+————+—————–+———–+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +————–+————+—————–+———–+ | art119…
Post: Getting History of Table Sizes in MySQL
December 8, 2010 By 13 Comments
… SELECT DATE(NOW()), TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES; I put it to the cron to run nightly as: 1 0 * * * mysql -u…,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES” Though if you’re looking to keep it completely inside MySQL you…
