MySQL Forums
Forum List  »  Performance

Sort Performance
Posted by: Jeff Hunter
Date: November 01, 2004 08:02PM

I'm running into an issue where having MySQL sort the results of a SELECT query adds a tremendous amount of time to the query when compared to returning unordered results for the same query. I was wondering if anyone else has seen this or has any ideas about what might be causing it.

First, here are some details about my system. I'm running a source code build of MySQL 4.0.17 on FreeBSD 5.1 (-RELEASE). Based on general recommendations to improve ORDER BY performance, I've increased sort_buffer_size to 8MB and read_rnd_buffer_size to 512K to make sure I've got plenty of memory for sorting.

Now, here's what I'm actually seeing. Here are the results of two queries:

mysql> select security, cusip, intrate, duedate from security where security like '%AEROPOSTA%';
+---------------------------------------+-----------+---------+---------+
| security | cusip | intrate | duedate |
+---------------------------------------+-----------+---------+---------+
| AEROPOSTALE COM | 007865108 | NULL | NULL |
| AEROPOSTALE COM | 7865108 | NULL | NULL |
| AEROPOSTALE INC COM | 007865108 | NULL | NULL |
| AEROPOSTALE | 007865108 | NULL | NULL |
| AEROPOSTALE INC | 007865108 | NULL | NULL |
| AEROPOSTALE | 7865108 | NULL | NULL |
| AEROPOSTALE INC | NULL | NULL | NULL |
| AEROPOSTALE INC | 7865108 | NULL | NULL |
| AEROPOSTALE INC COM | 7865108 | NULL | NULL |
| AEROPOSTALE COM STOCK SPLIT | 007865108 | NULL | NULL |
| AEROPOSTALE INC COM | NULL | NULL | NULL |
| AEROPOSTALE INCORPORATED-COMMON STOCK | 007865108 | NULL | NULL |
| AEROPOSTALE INCORPORATED | 007865108 | NULL | NULL |
+---------------------------------------+-----------+---------+---------+
13 rows in set (3.14 sec)

mysql> select security, cusip, intrate, duedate from security where security like '%AEROPOSTA%' order by security;
+---------------------------------------+-----------+---------+---------+
| security | cusip | intrate | duedate |
+---------------------------------------+-----------+---------+---------+
| AEROPOSTALE | 007865108 | NULL | NULL |
| AEROPOSTALE | 7865108 | NULL | NULL |
| AEROPOSTALE COM | 007865108 | NULL | NULL |
| AEROPOSTALE COM | 7865108 | NULL | NULL |
| AEROPOSTALE COM STOCK SPLIT | 007865108 | NULL | NULL |
| AEROPOSTALE INC | 007865108 | NULL | NULL |
| AEROPOSTALE INC | NULL | NULL | NULL |
| AEROPOSTALE INC | 7865108 | NULL | NULL |
| AEROPOSTALE INC COM | 007865108 | NULL | NULL |
| AEROPOSTALE INC COM | 7865108 | NULL | NULL |
| AEROPOSTALE INC COM | NULL | NULL | NULL |
| AEROPOSTALE INCORPORATED | 007865108 | NULL | NULL |
| AEROPOSTALE INCORPORATED-COMMON STOCK | 007865108 | NULL | NULL |
+---------------------------------------+-----------+---------+---------+
13 rows in set (27.20 sec)

Sorting a mere 13 rows adds more than 24 full seconds to the execution time. The security column is indexed, but again we're talking about all of 13 rows in the result set here. I can improve the performance by doing a fresh ANALYZE TABLE (the criteria is modified slightly to avoid query caching):

mysql> analyze table security;
+----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| invdb.security | analyze | status | OK |
+----------------+---------+----------+----------+
1 row in set (0.20 sec)

mysql> select security, cusip, intrate, duedate from security where security like '%AEROPOST%';
+---------------------------------------+-----------+---------+---------+
| security | cusip | intrate | duedate |
+---------------------------------------+-----------+---------+---------+
| AEROPOSTALE COM | 007865108 | NULL | NULL |
| AEROPOSTALE COM | 7865108 | NULL | NULL |
| AEROPOSTALE INC COM | 007865108 | NULL | NULL |
| AEROPOSTALE | 007865108 | NULL | NULL |
| AEROPOSTALE INC | 007865108 | NULL | NULL |
| AEROPOSTALE | 7865108 | NULL | NULL |
| AEROPOSTALE INC | NULL | NULL | NULL |
| AEROPOSTALE INC | 7865108 | NULL | NULL |
| AEROPOSTALE INC COM | 7865108 | NULL | NULL |
| AEROPOSTALE COM STOCK SPLIT | 007865108 | NULL | NULL |
| AEROPOSTALE INC COM | NULL | NULL | NULL |
| AEROPOSTALE INCORPORATED-COMMON STOCK | 007865108 | NULL | NULL |
| AEROPOSTALE INCORPORATED | 007865108 | NULL | NULL |
+---------------------------------------+-----------+---------+---------+
13 rows in set (1.30 sec)

mysql> select security, cusip, intrate, duedate from security where security like '%AEROPOST%' order by security;
+---------------------------------------+-----------+---------+---------+
| security | cusip | intrate | duedate |
+---------------------------------------+-----------+---------+---------+
| AEROPOSTALE | 007865108 | NULL | NULL |
| AEROPOSTALE | 7865108 | NULL | NULL |
| AEROPOSTALE COM | 007865108 | NULL | NULL |
| AEROPOSTALE COM | 7865108 | NULL | NULL |
| AEROPOSTALE COM STOCK SPLIT | 007865108 | NULL | NULL |
| AEROPOSTALE INC | 007865108 | NULL | NULL |
| AEROPOSTALE INC | NULL | NULL | NULL |
| AEROPOSTALE INC | 7865108 | NULL | NULL |
| AEROPOSTALE INC COM | 007865108 | NULL | NULL |
| AEROPOSTALE INC COM | 7865108 | NULL | NULL |
| AEROPOSTALE INC COM | NULL | NULL | NULL |
| AEROPOSTALE INCORPORATED | 007865108 | NULL | NULL |
| AEROPOSTALE INCORPORATED-COMMON STOCK | 007865108 | NULL | NULL |
+---------------------------------------+-----------+---------+---------+
13 rows in set (5.90 sec)


Still, > 5 seconds is an eternity for sorting 13 rows. My thought at this point is to maybe replace my build with a fresh one from the "official" FreeBSD ports collection in case there was something off with my initial configuration. Any other ideas?

Options: ReplyQuote


Subject
Views
Written By
Posted
Sort Performance
6154
November 01, 2004 08:02PM
2867
November 05, 2004 12:46AM
3466
November 09, 2004 03:04PM
2869
November 09, 2004 06:46PM
2777
November 09, 2004 07:56PM
3068
November 09, 2004 09:05PM
2719
November 05, 2004 01:44AM
2716
November 10, 2004 07:12PM


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.