Using Indexes to increase sort performance

MySQL & MariaDB Other MySQL® Questions
1

Hi there!

Very glad I discovered this forum and your blog! Some very interesting reads! I am working on my thesis for my IT-Bachelor and it involves performance, so any help is very much appreciated.

This may sound like an Amateur Question but I’m really mystified as to why MySQL chooses to use a Filesort and not my Index to process this statement:

SELECT * FROM Kontakt ORDER BY Email;

The table is created like this:

CREATE TABLE Kontakt (
id int(11) NOT NULL default ‘0’,
EMail varchar(255) NOT NULL default ‘’,
PRIMARY KEY (id),
KEY EMail (EMail)
) ENGINE=MyISAM;

I am using MySQL 5.0.22.

In the MySQL Reference it says that my select statement certainly would be using the index created…but it doesnt show when using explain and also performs slowly when having thousands of rows…

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization .html

Thanks for any hints…I don’t mind being stupid, as long as someone is able to explain it to me;)

Lars

2

Hi,

In this case you sort full table. Using index would require a lot of random lookups in the table to retrive the data itself which is why MySQL prefers to do the sort, in which case singe full table scan can be done (in certain cases MySQL Will even store all retrieved data in sort file so no extra lookups will be needed)

If you want to force index to be used add LIMIT clause, something like LIMIT 1000000000 to make sure all rows are returned.

3

Thanks for the insight! It helped me understand some things about how and when indizes are used. The problem was not that the limit clause was missing, (this helped as long as the limit i set was lower than the actual rows the table contained, be careful with test-data;) it was the SQL_CALC_FOUND_ROWS flag i set. This actually virtualizes the limit. tricky stuff.