… 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_…MySQL which indexes would it use. At the end of the process you’ll have an output with a list of not used indexes. …
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
… with index the time is by 3.5 times slower. Good that mysql in this case choose do not use index mysql> explain select count(name) from testr where has_something=0; +—-+————-+——-+——+—————+——+———+——+———-+————-+ | id | select_type | table | type | possible…
Post: Using Sphinx as MySQL data retrieval accelerator
October 30, 2006 By 25 Comments
… efficient indexes so selectivity is done by index and index used to resolve order by. Funny enough to filter by attributes or sort sphinx does not use indexes – indexes are only used…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… choice but only to use combined index: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index(i1,i2) where i1… you do not want it to run, however I do not know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used. I…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… ORDER BY looks like: SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT N,M Make sure it uses index It is very important to… which does not use indexes, even if it is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select_type | table…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…: Using intersect(user_type,status,parent_id); Using where; Using index; Using filesort At first glance, this might not look too bad. MySQL is using three different indexes… the query like so, with the following result: SELECT user_id FROM users USE INDEX(user_type) WHERE user_type=2 AND user…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… which can use index can also use prefix of the new index are they ? It turns there are special cases when this is not the… | const,const | 120640 | Using index | +—-+————-+———-+——+—————+——+———+————-+——–+————-+ 1 row in set (0.00 sec) Wow. The query runs 30 times faster – not only because it… it estimates it will scan 2247 rows in the selected plan, while using (a) index you can get result scanning only 1 row…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… indexes in all_indexes, but not in used_indexes. These indexes (if our original index statistics are good) are candidates to be dropped: create view droppable_indexes as select all_indexes…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…. While a covering index (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index) is according… will perform by only looking at the execution plan is not enough, we must run both queries and compare execution time… select_type: SIMPLE table: employees type: index possible_keys: NULL key: idx_first key_len: 16 ref: NULL rows: 300584 Extra: Using index The…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… index on column using order by clause. A: If index is used for ORDER BY the same index must be used for selection for the same table, not other index, also you only can have equality comparison as a search condition WHERE A=5 ORDER BY B will use index (A…
