MySQL Forums
Forum List  »  Newbie

Very slow sort on Order By for MySQL query.
Posted by: SlowQuery Shark
Date: June 26, 2011 12:01AM

Hi everybody! :-D

I have a problem on sorting fields. The Order By clause on a query is very slow on a table of 2 million rows - even when I have an index on the field.

Here is the table structure, I need to sort the data quickly by 'id', 'fld1' or 'fld2' depending on search criteria.

CREATE TABLE `tblM` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fld1` varchar(1024) NOT NULL,
`fld2` varchar(1024) NOT NULL,
PRIMARY KEY (`id`),
KEY `fld1` (`fld1`(1000)),
KEY `fld2` (`fld2`(1000)),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21748375 ;

The query I use is:
SELECT id, fld1, fld2 FROM tblM ORDER BY fld2 ASC LIMIT 30

However this takes around 15 seconds to return a result. However when I short by id (the primary key) it takes only 0.0017 sec. Why is this?

How can I make this sort through the data of those fields in less than a second? Is this possible in MySQL? (Disk space does not matter, but the fact to order these rows in less than a second does. I tried looking at documentation and various other forums but nobody seems to know how to make it sort these fields in <1sec)



Edited 3 time(s). Last edit at 06/26/2011 12:36AM by SlowQuery Shark.

Options: ReplyQuote


Subject
Written By
Posted
Very slow sort on Order By for MySQL query.
June 26, 2011 12:01AM


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.