MySQL Forums
Forum List  »  Performance

Re: sorting takes more time
Posted by: Harrison Fisk
Date: March 14, 2005 02:18AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2798
March 14, 2005 12:44AM
Re: sorting takes more time
1413
March 14, 2005 02:18AM


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.