Re: Index usage in 4.1.7
Posted by: F de Bruijn
Date: December 14, 2004 02:41AM

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"

Options: ReplyQuote


Subject
Views
Written By
Posted
8709
December 10, 2004 10:03AM
4313
December 13, 2004 05:47PM
Re: Index usage in 4.1.7
4356
December 14, 2004 02:41AM
3653
January 27, 2005 03:27PM
3805
February 15, 2005 03:48PM
3364
November 23, 2005 04:48PM
3895
March 22, 2005 08:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.