Re: sorting takes more time

[Harrison Fisk]

Hi,

There are a few things you can do to increase sorting speed.

1. Try to get it to use an index to do the sort. However if you are retrieving the entire table, then it won't do this as it would result in too much disk activity.

2. Increase the amount of memory available for doing the sort. The setting for this is sort_buffer_size. sort_buffer_size can be set on a per-session basis or globally. Keep in mind that it can be allocated once for each sort being done, so it might use quite a bit of memory even if it set to a small size. Try setting it to larger for the thread before you do your query:

SET SESSION sort_buffer_size = 50*1024*1024;
SELECT * FROM tbl ORDER BY a;

That would use 50MB for doing the sort, which should be significantly faster than the default 2MB.

3. Depending on how the data is loaded in and table type, you could use the ALTER TABLE ... ORDER BY ...; to put the data in order internally. This should speed up sorting since the data is mostly in the correct order to begin with.

Hope that helps!

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com