List:General Discussion« Previous MessageNext Message »
From:johnt Date:November 21 2002 10:28pm
Subject:irrelevant multi-column index yields incredible speedup!
View as plain text  
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
Thread
irrelevant multi-column index yields incredible speedup!johnt21 Nov
  • Re: irrelevant multi-column index yields incredible speedup!Dan Nelson22 Nov
    • Re: irrelevant multi-column index yields incredible speedup!johnt22 Nov