…data_length),2) idxfrac FROM information_schema.TABLES WHERE table_name like “%performance_log%”; +——–+———+———+——-+————+———+ | tables | rows | data | idx…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
…data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows | data | idx…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…mysql> select * from all_indexes limit 1 \G *************************** 1. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: actor INDEX_NAME: idx…
Comment: Finding out largest tables on MySQL Server
October 6, 2008 By 27 Comments
…data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT 10; +———————————————-+——–+——-+——-+————+———+ | concat(table_schema,’.',table_name) | rows | DATA | idx…
Comment: Finding out largest tables on MySQL Server
October 14, 2008 By 27 Comments
…data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows | DATA | idx…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 27 Comments
…data, I have for example a table that looks like this from the query in this article: +—————————————+———+——–+——–+————+———+ | CONCAT(table_schema, ‘.’, table_name) | rows | DATA | idx…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx_first) ORDER BY first_name\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: …optimizer: it does not know on which kind of media data is stored. If it is stored on spinning disks, …
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
… | li_shp_dt_idx | li_shp_dt_idx | 3 | NULL | 8272067 | Using where | +—-+————-+———-+——-+—————+—————+———+——+———+————-+ against table CREATE TABLE `lineitem` ( `… query, as MySQL used index to scan rows and then did access to data to read…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…table. mysql> SELECT * FROM INDEX_STATISTICS; +————–+————+————–+———–+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS… | idx_orders | 2832 | | tpcc | customer | idx…data on INNODB_INDEX_STATS to get the list of unused indexes: mysql> SELECT INNODB_INDEX_STATS.TABLE…
Post: Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
… First is when all data, indexes and system … mysql> select * from INNODB_SYS_INDEXES WHERE TABLE_ID = 13G *************************** 1. row *************************** TABLE…row *************************** TABLE_ID: 13 ID: 16 NAME: idx_actor_last_name N_FIELDS: 1 TYPE: 0 SPACE: 15 PAGE_NO: 4 2 rows…
