| Using Indexes to increase sort performance [message #17] |
Sat, 12 August 2006 07:11  |
 elektronaut
Messages: 9
Registered: August 2006
Location: Switzerland - Zürich - W... |
Junior Member |
|
|
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
Minds are like parachutes - they work best when open.
|
|
|
| Re: Using Indexes to increase sort performance [message #24 is a reply to message #17 ] |
Sun, 13 August 2006 09:30  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: Using Indexes to increase sort performance [message #34 is a reply to message #24 ] |
Sun, 13 August 2006 12:30 |
elektronaut
Messages: 9
Registered: August 2006
Location: Switzerland - Zürich - W... |
Junior Member |
|
|
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.
Minds are like parachutes - they work best when open.
|
|
|
Members
Search
Help
Register
Login
Home
