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 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
