MySQL Forums
Forum List  »  Optimizer & Parser

multiple-column index and order by
Posted by: Raphael Hoffmann
Date: July 06, 2012 05:59PM

I'm confused --- Why can't MySQL use the index in the following example?

I am assuming that the tuples are physically stored by the multiple-column primary key.
So why can't it jump to t.col1 = 16, and then simply grab the next 100 tuples?
Instead, it is reading all tuples with t.col1 = 16 and then sorting them?!?


CREATE TABLE `mytable` (
`col1` smallint(6) NOT NULL,
`col2` int(11) NOT NULL,
`col3` tinyint(4) NOT NULL,
`col4` tinyint(4) NOT NULL,
PRIMARY KEY (`col1`,`col2`,`col3`,`col4`)
) ENGINE=MyISAM;

mysql> explain SELECT * FROM mytable t WHERE t.col1 = 16 ORDER BY t.col2 LIMIT 100;

+----+-------------+-------+------+---------------+---------+---------+-------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+---------+--------------------------+
| 1 | SIMPLE | di2b | ref | PRIMARY | PRIMARY | 2 | const | 5190118 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+---------+--------------------------+

Thanks.

Raphael

Options: ReplyQuote


Subject
Views
Written By
Posted
multiple-column index and order by
2962
July 06, 2012 05:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.