…indexes: mysql> SELECT INNODB_INDEX_STATS.TABLE_NAME, INNODB_INDEX_STATS.INDEX_NAME from INNODB_INDEX_STATS WHERE CONCAT(INNODB_INDEX_STATS.index_name, INNODB_INDEX_STATS.table_name)NOT IN(SELECT CONCAT(index_statistics.index_name, index… pt-index-usage tries to convert non-SELECT queries to SELECT …
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…INDEX_NAME from slave_index_stats) ORDER BY TABLE_SCHEMA, TABLE_NAME; Now I can query the ‘all_known_index_usage…indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index_name as index_name, ROUND(all_indexes.index…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… can one have optimized indexes and sorting in such a scenario, since inequality ends index usage. Actually in this case index on (A,B…: If index is used for ORDER BY the same index must be used for selection for the same table, not other index, also you… into IN-ranges in some cases for better index usage. Q: Is the b+ tree innodb index a single or double linked list at…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
… CPU usage is only about 50%, leaving other 50% in idle state. First problem is index->lock mutex. InnoDB uses single mutex per index, so when you run mixed read / write queries, InnoDB locks index for write operation and thus keeps all selects…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” …KEY `role_id_note` (`role_id`,`note`) ) ENGINE=InnoDB AUTO_INCREMENT=22187769 DEFAULT CHARSET=utf8; …algorithms, statistics computation and monitoring variables about index usage in the future, now that we …
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
…index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index… use full index for sorting (note MySQL may not select to use index for sort… index to read data Some storage engines (MyISAM and Innodb included) can also use index…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984…INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL 3 rows in set (0.00 sec) Innodb Memory Usage…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… see how efficient storage engines are in terms of CPU usage. In real life workload results are likely to be very…. InnoDB is better than MyISAM by 87% in case with 4 threads but drops down very fast. READ_PK_POINT_INDEX Query: SELECT id FROM $tableName WHERE id = %d Simple but very quick query to retrieve value from PK. The results for InnoDB…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…InnoDB 442 mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select…much insert buffer efficiency. Adaptive hash index is hash index Innodb builds for some pages to … activity and memory usage. You can see total memory allocated by Innodb (sometimes it …
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
…) 2. There is table with statistic per index: mysql> select * from table_io_waits_summary_by_index_usage where OBJECT_NAME=’sbtest55′G *************************** 1…_NAME: /data/tachion/sb/ibdata1 EVENT_NAME: wait/io/file/innodb/innodb_data_file COUNT_READ: 118218 COUNT_WRITE: 849692 SUM_NUMBER…
