MySQL Forums
Forum List  »  Performance

Re: Not using index properly in 4.1.7
Posted by: F de Bruijn
Date: December 07, 2004 09:47AM

Hi,

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
3106
December 07, 2004 03:22AM
1781
December 07, 2004 08:41AM
Re: Not using index properly in 4.1.7
1858
December 07, 2004 09:47AM
1942
December 08, 2004 11:31AM
1897
December 08, 2004 01:51PM
1740
December 09, 2004 09:50AM


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.