Sort Performance

[Jeff Hunter]

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?