….TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA….TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics IST ON CONCAT_WS(‘.’, s.TABLE_SCHEMA…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
…, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES; +——–+———-+———+——–+————+———+ | tables | rows | data | idx | total_size | idxfrac | +——–+———-+———+——–+————+———+ | 1538 | 1623…) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; +——–+——————–+——-+——-+——-+————+———+ | tables | table_schema | rows | data…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema… and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE…
Post: Google's user_statistics V2 port and changes
September 12, 2008 By 9 Comments
…: 70748 and INDEX/TABLE statistics: mysql> select * from information_schema.index_STATISTICS limit 10; +————–+————+—————–+———–+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +————–+————+—————–+———–+ | art119… in set (0.00 sec) mysql> select * from information_schema.table_STATISTICS limit 10; +————–+—————–+———–+————–+————————+ | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
…_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows | data… quite a lot about your schema this way. It is also worth to note queries on information_schema can be rather slow if…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… improvement on Percona Server that adds some tables to Information Schema with useful information to understand the server activity and identify the source… task of finding unused indexes we’re going to use INFORMATION_SCHEMA.INDEX_STATISTICS but first we should enable User Statistics: mysql…
Post: New patches, new builds
September 5, 2008 By 24 Comments
… added tables USER_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS to INFORMATION_SCHEMA mysql> select * from information_schema.table_statistics; +————————-+———–+————–+———————-+ | TABLE_NAME | ROWS_READ | ROWS_CHANGED… TIME_MS to INFORMATION_SCHEMA.PROCESSLIST which contains time of query execution with microseconds granularity. mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +—-+——+———–+——+———+——+———–+———————————————-+———+ | ID | USER…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… FROM information_schema.table_constraints a INNER JOIN information_schema.key_column_usage b ON a.constraint_name = b.constraint_name AND a.table_schema… as referenced_column_name FROM information_schema.statistics WHERE non_unique = 1 AND table_schema=’test’ GROUP BY table_schema, table_name, constraint_name…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… not provide any information of this type, Percona Server however adds number of tables to Information Schema which makes this information possible. It is just few queries away: SELECT `schema` AS table_schema, innodb_sys_tables…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
….TABLE_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… information_schema.table_statistics ts where ts.table_schema=s.table_schema and ts.table_name=s.table_name)>0) d group by table_schema…
