…(11) DEFAULT NULL, `status` enum(‘archived’,'active’) DEFAULT NULL, PRIMARY KEY (`id`), KEY `status` (`status`) ) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET… find someway to parse and EXPLAIN all results, then subtract the indexes that were mentioned from all indexes known. There’s an…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…’d wonder why the EXPLAIN plan for a given SELECT would show N possible index choices but only one index actually used. To…, and an EXPLAIN produced the following execution plan: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: index_merge possible_keys: PRIMARY…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…_first index as a relevant choice (possible_keys: NULL). What do we get if we force the optimizer to use the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx…> explain SELECT first_name FROM employees ORDER BY first_name\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees type: index possible_keys…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
… type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 6 Extra: 1 row in set (0.00 sec) mysql> explain select… key value so primary key value can be read from index, making some queries index covered which previously was not: Notice “Using Index” difference mysql> explain…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… ? Is it expected to rebuild index by sort of key_cache as it can for MyISAM. EXPLAIN may take long time In MySQL… | Using index | +—-+————-+——-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) This statement obviously will not scan more than 10 rows but EXPLAIN shows…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat_id) where cat_id=5 and seller… select_type: SIMPLE table: goods type: ref possible_keys: cat_id key: cat_id key_len: 4 ref: const rows: 989171 Extra: Using… rows to display only few. If we force index as in second query explain will look scary with estimated million of rows…
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 from… 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: Why Index could refuse to work ?
September 8, 2006 By 15 Comments
… is mentioning it in “possible keys” ? Should you try to force it ? mysql> explain select * from article force index (PRIMARY) where article_id=10; +—-+————-+———+——+—————+——+———+——+——-+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows…
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… multiple key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… testing, as I said before, index condition pushdown. Let’s have a look at the EXPLAIN output: mysql> EXPLAIN SELECT * FROM cast_info… type: ref possible_keys: role_id_note key: role_id_note key_len: 4 ref: const rows: 10259274 Extra: Using index condition 1 row… that whenever the covering index technique is available, this is always preferred over the ICP optimization: mysql> EXPLAIN SELECT role_id FROM…
