…’d wonder why the EXPLAIN plan for a given SELECT would show N possible index choices but only one index actually used. To… an EXPLAIN on your SELECT and you’ll see “index_merge” as the type of query and the type of index merge algorithm… ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE user_type=2 AND…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…  226 | +—————————–+ 1 row in set (0.05 sec) mysql> EXPLAIN SELECT * FROM country WHERE population > 1000; # query 3 +—-+————-+———+——+—————+——+———+——+——+————-+ | id | select… find someway to parse and EXPLAIN all results, then subtract the indexes that were mentioned from all indexes known. There’s an…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… between a covering index and a full index scan in the EXPLAIN output. While a covering index (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
…`,`price`), KEY `cat_id_2` (`cat_id`,`seller_id` ) mysql> explain select * from goods where cat_id=5 and seller_id… 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat_id) where cat_id=5 and seller… rows to display only few. If we force index as in second query explain will look scary with estimated million of rows…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…_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… understand. pt-index-usage reads the slow query log and execute every query with EXPLAIN to ask MySQL which indexes would it use…-index-usage uses EXPLAIN and User Statistics number of rows read for every index. So we’re comparing an estimation with real data. EXPLAIN…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… such case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the index order. It is very… only reading index and not accessing rows you will see “using index” in EXPLAIN output. These are the main “core” use for indexes. You can also see others like using index for group…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… lets see if index can be used as covering index if it has some key parts which are prefixes: mysql> explain select k… covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index” in Extra column. mysql> explain select… the index but MySQL does not look at the actual data in this case it only looks at definitions. mysql> explain select…
Post: When EXPLAIN can be misleading
November 12, 2006 By 1 Comment
…> explain select * from rnd order by r desc limit 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rnd type: index… ref: NULL rows: 49280 Extra: Using index 1 row in set (0.00 sec) This EXPLAIN estimates there would be almost 50…-using-indexes is enabled which may flood it quite badly. Too bad –min-examined-row-limit is not yet implemented 
EXPLAIN would…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… from the index. MySQL can ether read index only for all rows, in this case you will see “Using Index” in EXPLAIN output or it will read row data for all rows – it can’t read Index and perform row… part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain SELECT…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously discussed… testing, as I said before, index condition pushdown. Let’s have a look at the EXPLAIN output: mysql> EXPLAIN SELECT * FROM cast_info… that whenever the covering index technique is available, this is always preferred over the ICP optimization: mysql> EXPLAIN SELECT role_id FROM…
