The EXPLAIN command is an important tool to review how a SQL query is executed and in this example includes what indexes are used.
By adding a covering index I ended up with the following EXPLAIN plan I was unable to explain. The end result was a boost in server performance which was the ultimate goal.
mysql> explain select max(md) from e_r where email = '[email protected]' and id = '36981'; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ 1 row in set (0.00 sec)
The queries still produced the expected results.
That’s a weird one you got there
What storage engine is it and what version of MySQL?
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
“# Select tables optimized away
The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.”
You will also see:
+—-+————-+——-+——+—————+——+———+——+——+————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+————————-+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row |
If you search for row that doesn’t exist.
I should probably explain more.
MySQL can not make use of an index beyond where range type stuff exists for the retrieval of matching rows. An example would be if you had a table with an index on (a,b,c). If you had a query with a where clause like:
where a =1 and b=2, and c=3
you will notice that the index length is greater on the explain than
where a =1 and b>2, and c=3
because the c portion of the index is not used.
For a covering index one is retrieving all the needed data from the index without hitting the underling table. For most cases this is somewhat equivalent to a sequential scan of a table, however whatever index will probably be much more efficient to scan compared to the its table.
For the general case of indexes where the index is being used to find matching rows the query does not need to scan the index to check each pseudo row against the where clause as there are more efficient ways to traverse the index. Think sorting algorithms…
A covering index can also make use of the optimizations for general index usage and examine for less of the index than would be required a normal sequential scan of the index. You will notice the index length field in the explain change. A quick example:
create table e_r(email varchar(25),id int, md int);create table integers(i int);
insert into e_r select unhex(round(rand()*pow(8,32))), round(rand()*100000000), round(rand()*100000000) from integers i1, integers i2,integers i3, integers i4,integers i5;
insert into e_r values(‘[email protected]’,’36981′,42);
mysql> explain select max(md) from e_r where email = ‘[email protected]’ and id > ‘36981’;
+—-+————-+——-+——-+—————+——-+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——-+———+——+——+————————–+
| 1 | SIMPLE | e_r | range | email | email | 33 | NULL | 1 | Using where; Using index |
+—-+————-+——-+——-+—————+——-+———+——+——+————————–+
1 row in set (0.00 sec)
mysql> explain select max(md) from e_r where email > ‘[email protected]’ and id = ‘36981’;
+—-+————-+——-+——-+—————+——-+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——-+———+——+——+————————–+
| 1 | SIMPLE | e_r | range | email | email | 28 | NULL | 5080 | Using where; Using index |
+—-+————-+——-+——-+—————+——-+———+——+——+————————–+
1 row in set (0.00 sec)
And the special case you are seeing:
mysql> explain select max(md) from e_r where email = ‘[email protected]’ and id = ‘36981’;
+—-+————-+——-+——+—————+——+———+——+——+——————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——————————+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+—-+————-+——-+——+—————+——+———+——+——+——————————+
1 row in set (0.00 sec)
So to conclude what is happening here: MySQL does not need to touch the underlying table because of a covering index, and furthermore because of the order of the columns in the index and how the WHERE/SELECT clauses a full scan of the index is not needed and as only a single row is returned you will see the above explain.
It is about bed time so forgive me if this is less than crystal clear.
Oh, I forgot to include index that is used:
alter table e_r add index(email,id,md)
@Josh, This was 5.0.77 on Ubuntu 9.04 The storage engine was InnoDB
That appears in the Extra column when a MAX/MIN/COUNT function was replaced by a constant. Look in the source code for opt_sum_query(). It returns a 1 when all Items were resolved. This is possible when an index can return all the information the MAX/MIN/COUNT function needs, which the covering index did
Cheers!
Jay
Just do EXPLAIN EXTENDED and then SHOW WARNINGSG
You’ll see that the rewritten query contains all constants. So the query no longer needs to be executed, it’s already been resolved in the optimiser phase.
When the optimiser finds that only one row can match, it just retrieves that row and replaces all referencing columns with constants. That way tables can disappear and thus require fewer (or in this case no) joins. It does not require a covering index.
In my post from a few months ago (http://openquery.com/blog/innodb-lock-timeout-before-query-execution) I had an example:
EXPLAIN EXTENDED SELECT name from Country where code=’AUS’
you would see access type const in the explain (which is the indicator for this optimisation), and
SHOW WARNINGS
brings up
select ‘Australia’ AS `name` from `world`.`country` where 1
All constants by the time the optimiser has done its thing…