# Using Indexes to increase sort performance

**URL:** <https://forums.percona.com/t/using-indexes-to-increase-sort-performance/58>\
**Category:** Other MySQL® Questions\
**Created:** [August 12, 2006, 5:41am UTC](https://forums.percona.com/t/using-indexes-to-increase-sort-performance/58 "2006-08-12T05:41:40Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![elektronaut](https://avatars.discourse-cdn.com/v4/letter/e/54ee81/32.png) [@elektronaut](https://forums.percona.com/u/elektronaut)\
**Post date:** [August 12, 2006, 5:41am UTC](https://forums.percona.com/t/using-indexes-to-increase-sort-performance/58/1 "2006-08-12T05:41:40Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [August 13, 2006, 8:00am UTC](https://forums.percona.com/t/using-indexes-to-increase-sort-performance/58/2 "2006-08-13T08:00:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![elektronaut](https://avatars.discourse-cdn.com/v4/letter/e/54ee81/32.png) [@elektronaut](https://forums.percona.com/u/elektronaut)\
**Post date:** [August 13, 2006, 11:00am UTC](https://forums.percona.com/t/using-indexes-to-increase-sort-performance/58/3 "2006-08-13T11:00:48Z")

</div>

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.