Re: Index usage in 4.1.7
Hi,
Thanks for your reaction.
The table structure I use for my table is listed below. Also I've included an explain for MySQL version 4.0 and 4.1. As you can see in 4.0 the query took 00.21 sec. and in 4.1 it took much longer (47.59 sec.). This is dramatic :(. Is there no change in the 4.1.7 version of MySQL that is related to the usage of indexes ?
CREATE TABLE example_table (
`id` MEDIUMINT ( 8 ) UNSIGNED NOT NULL DEFAULT '0',
`t1` text NOT NULL,
`t2` text NOT NULL,
`t3` text NOT NULL,
`t4` text NOT NULL,
`t5` text NOT NULL,
`t6` text NOT NULL,
`t7` text NOT NULL,
`t8` text NOT NULL,
`t9` text NOT NULL,
`t10` text NOT NULL,
`t11` text NOT NULL,
`count1` DOUBLE DEFAULT NULL,
`count2` DOUBLE DEFAULT NULL,
PRIMARY KEY ( `id`,
`t3` ( 20 ),
`t4` ( 20 ),
`t5` ( 20 ),
`t6` ( 20 ),
`t7` ( 20 ),
`t8` ( 20 ),
`t9` ( 20 ) ),
KEY `key` ( `t1` ( 5 ),
`t2` ( 2 ),
`t3` ( 20 ),
`t4` ( 20 ),
`t5` ( 20 ),
`t6` ( 20 ),
`t7` ( 20 ),
`t8` ( 20 ),
`t9` ( 20 ),
`t10` ( 20 ),
`t11` ( 20 ) )
) ENGINE = MyISAM DEFAULT CHARSET = latin1;
##Explain for MySQL version 4.0.16
##Query excecution (without explain) : 0:00.21
##Number of rows table : 6892475
##Number of rows output : 1
EXPLAIN SELECT t1,t2,SUM(count1),SUM(count2),
t3,t4,t5,t6,t7,t8,t9,t10,t11
FROM table
WHERE (t1 IN ('STRING1') AND t2 IN ('STRING2')) AND
(
(t3 IN ('STRING3')) AND
(t4 IN ('STRING4')) AND
(t5 IN ('STRING5')) AND
(t6 IN ('STRING6')) AND
(t7 IN ('STRING7')) AND
(t8 IN ('STRING8')) AND
(t9 IN ('STRING9')) AND
(t10 IN ('STRING10')) AND
(t11 IN ('STRING11'))
)
GROUP BY t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11
ORDER BY NULL
"#"|"table"|"type"|"possible_keys"|"key"|"key_len"|"ref"|"rows"|"Extra"
"1"|"table"|"range"|"key"|"key"|"212"|"{null}"|"1"|"Using where; Using temporary"
##Explain for MySQL version 4.1.7
##Query excecution (without explain) : 0:47.59
##Number of rows table : 6892475
##Number of rows output : 1
EXPLAIN SELECT t1,t2,SUM(count1),SUM(count2),
t3,t4,t5,t6,t7,t8,t9,t10,t11
FROM table
WHERE (t1 IN ('STRING1') AND t2 IN ('STRING2')) AND
(
(t3 IN ('STRING3')) AND
(t4 IN ('STRING4')) AND
(t5 IN ('STRING5')) AND
(t6 IN ('STRING6')) AND
(t7 IN ('STRING7')) AND
(t8 IN ('STRING8')) AND
(t9 IN ('STRING9')) AND
(t10 IN ('STRING10')) AND
(t11 IN ('STRING11'))
)
GROUP BY t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11
ORDER BY NULL
"#"|"id"|"select_type"|"table"|"type"|"possible_keys"|"key"|"key_len"|"ref"|"rows"|"Extra"
"1"|"1"|"SIMPLE"|"table"|"ALL"|"key"|"{null}"|"{null}"|"{null}"|"6892475"|"Using where; Using temporary"