Hello,
Adding an irrelevant second column to an index instead of
just a single column index gives an incredible speedup and
I don't know why!
(server version: 3.23.53a running P133 32 megs ram - not a speedy
machine)
I have a bunch of records with the relevant columns being:
id bigint(20) unsigned NOT NULL auto_increment,
state char(2) NOT NULL default '',
modified date default NULL,
textthing text,
If I put a SINGLE COLUMN index just on 'state', I get the follwing
explain and timings (all timings were run right in a row, I'm the
only one using the machine):
mysql> explain SELECT id FROM mytable WHERE textthing LIKE '%sljdfls%' AND state =
> 'TX';
+---------+------+---------------+-------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+-------+---------+-------+------+------------+
| mytable | ref | state | state | 2 | const | 516 | where used |
+---------+------+---------------+-------+---------+-------+------+------------+
1 row in set (0.03 sec)
cpu=0.050 elpsd=0:20.01
cpu=0.020 elpsd=0:19.28
cpu=0.050 elpsd=0:19.68
cpu=0.030 elpsd=0:18.71
cpu=0.050 elpsd=0:17.50
cpu=0.020 elpsd=0:20.63
cpu=0.030 elpsd=0:19.81
cpu=0.050 elpsd=0:24.36
cpu=0.030 elpsd=0:21.32
cpu=0.010 elpsd=0:17.75
If I put a MULTI-column index on state (state, modified)
I get these timings. It shows less rows to be searched(?) and
'modified' isn't even used anywhere! What gives?
mysql> explain SELECT id FROM mytable WHERE textthing LIKE '%sljdfls%' AND state =
> 'TX';
+---------+------+---------------+-------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+-------+---------+-------+------+------------+
| mytable | ref | state | state | 2 | const | 413 | where used |
+---------+------+---------------+-------+---------+-------+------+------------+
1 row in set (0.01 sec)
cpu=0.040 elpsd=0:15.05
cpu=0.010 elpsd=0:09.09
cpu=0.030 elpsd=0:06.57
cpu=0.030 elpsd=0:05.73
cpu=0.030 elpsd=0:03.90
cpu=0.060 elpsd=0:03.25
cpu=0.000 elpsd=0:02.62
cpu=0.040 elpsd=0:00.66
cpu=0.020 elpsd=0:00.63
cpu=0.020 elpsd=0:00.63
Is there a special cache going on somewhere? I would really like to
understand this.
John
