Re: Sort Performance

[Jeff Hunter]

Here is the output of SHOW CREATE TABLE:

CREATE TABLE `security` (
`id` int(11) NOT NULL auto_increment,
`history_only` char(1) NOT NULL default 'N',
`security` varchar(255) NOT NULL default '',
`cusip` varchar(255) default NULL,
`duedate` date default NULL,
`intrate` varchar(255) default NULL,
`publish_date` datetime NOT NULL default '0000-00-00 00:00:00',
`last_modified` timestamp(14) NOT NULL,
`last_modified_by` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `security` (`security`),
KEY `cusip` (`cusip`)
) TYPE=InnoDB


There are currently 516,727 rows in the table.

Here is the output from EXPLAIN for both the natural order and sorted statements:

mysql> explain select security, cusip, intrate, duedate from security where security like '%AEROPOST%';
+----------+------+---------------+------+---------+------+--------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+------+---------------+------+---------+------+--------+-------------+
| security | ALL | NULL | NULL | NULL | NULL | 514365 | Using where |
+----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select security, cusip, intrate, duedate from security where security like '%AEROPOST%' order by security;
+----------+-------+---------------+----------+---------+------+--------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+-------+---------------+----------+---------+------+--------+-------------+
| security | index | NULL | security | 255 | NULL | 514365 | Using where |
+----------+-------+---------------+----------+---------+------+--------+-------------+
1 row in set (0.00 sec)

I tried the IGNORE INDEX hint, but as you can see, that makes the results consistently much worse:

mysql> select security, cusip, intrate, duedate from security ignore index (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 |
| AEROPOSTALE INC | 007865108 | NULL | 1899-12-30 |
| AEROPOSTALE INC | 007865108 | 0.0000 | NULL |
+---------------------------------------+-----------+---------+------------+
15 rows in set (5.46 sec)

mysql> select security, cusip, intrate, duedate from security ignore index (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 | 007865108 | NULL | 1899-12-30 |
| AEROPOSTALE INC | 007865108 | 0.0000 | 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 |
+---------------------------------------+-----------+---------+------------+
15 rows in set (27.07 sec)