Re: Sort Performance
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)
Subject
Views
Written By
Posted
6445
November 01, 2004 08:02PM
2987
November 05, 2004 12:46AM
Re: Sort Performance
3571
November 09, 2004 03:04PM
2978
November 09, 2004 06:46PM
2900
November 09, 2004 07:56PM
3235
November 09, 2004 09:05PM
2827
November 05, 2004 01:44AM
2845
November 10, 2004 07:12PM