How can I sort records in MySQL quickly?

I have a database of 2,000,000+ records. I need to be able to sort by any of the 30 fields in the table quickly.

I tired adding an index(s) but it did not seem to increase the speed of the order by clause.

Here is my table structure:

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开发者_StackOverflow` (`fld1`(1000)),
 KEY `fld2` (`fld2`(1000)),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21748375 ; 

This is an example query I run:

SELECT id, fld1, fld2 FROM tblM ORDER BY fld2 ASC LIMIT 30 

However this takes around 15 seconds to execute. Is there any way in which I can these types of queries execute in < 1 sec?

Sorting by primary key is only fast takes 0.0017 secs, it would be nice to have the other fields with the same performance. Disk space does not matter.

---

1. I don't think an index would matter if you're not applying a filter (where clause)
2. 2,000,000 rows with fields the size of 1024 is no small table, so some of this will come down to hardware
3. What is the speed if you run it again? Perhaps caching will improve its performance.
4. Can you use InnoDB instead of MyISAM?

Edit

---

1. This article was written some time ago, so I'm not sure it still applies, but it was informative when it came out. It discusses the difference between InnoDB and MyISAM, regarding clustered indexes. http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

2. Try using USE INDEX or FORCE INDEX:

    SELECT id, fld1, fld2 FROM tblM USE INDEX (fld2) ORDER BY fld2 ASC LIMIT 30
   

   or

    SELECT id, fld1, fld2 FROM tblM FORCE INDEX (fld2) ORDER BY fld2 ASC LIMIT 30
   

---

i think you must create index for those fields,it will reduce your query time.

---

Try changing engine from MYISAM to INNODB.

---

you're trying to sort on a 1024 length varchar? that's a lot of potential work to do...

can you get away with perhaps sorting for the first 10 characters? I haven't tested this in MySQL, but if you created a fld1_short field that was varchar(10) and populate it with the first 10 characters of fld1, and create an index on it, it might give better performance.

You might want to read up on the MySQL doco regarding ORDER BY optimisation - you could well be hitting your sort_buffer_size and/or read_rnd_buffer_size settings, given the amount of data you're working with.

---

You could create an index in the right order that includes all the fields you're selecting. So for your query:

SELECT id, fld1, fld2 FROM tblM ORDER BY fld2 ASC LIMIT 30 

You could create this index:

create index ix_tblM_fld2 on tblM (fld2, fld1, id)

One word of advice re this:

Sorting by primary key is only fast takes 0.0017 secs, it would be nice to have the other fields with the same performance. Disk space does not matter.

In order for the database to work with your data, it has to be loaded into memory. Loading into memory is a slow operation, and RAM is generally not unlimited. Creating too many indexes can significantly burden a server.