-none- 2007-10-02 - By Eamon Daly
Back
Hi, all. I couldn't find this mentioned in the docs or in
the archives, so I'd figure I'd ask. I have a table with a
multipart index on three columns. When querying the table
alone using IN operators on any of the three columns, all
parts of the index are used. However, if I do a JOIN with
another table on the first column, the first part of the
index is used, but not the rest-- but only when searching
for multiple values on col2. Best explained by example, so
here's the table:
CREATE TABLE `table1` (
`col1` char(1) default NULL,
`col2` char(1) default NULL,
`col3` char(1) default NULL,
KEY `col1` (`col1`,`col2`,`col3`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
So with multiple IN operators against table1 alone, EXPLAIN
gives the expected key_len of 6:
EXPLAIN
SELECT SQL_NO_CACHE COUNT(*)
FROM table1
WHERE table1.col1 IN ('A', 'B') AND table1.col2 IN ('A', 'B') AND
table1.col3 IN ('A', 'B')
and if I JOIN against another table with single values in
the IN operators, I again get a key_len of 6:
EXPLAIN
SELECT SQL_NO_CACHE COUNT(*)
FROM table1, table2
WHERE table1.col1 = table2.col1 AND table1.col2 IN ('A') AND table1.col3 IN
('A')
This one, however, results in a key_len of 2:
EXPLAIN
SELECT SQL_NO_CACHE COUNT(*)
FROM table1, table2
WHERE table1.col1 = table2.col1 AND table1.col2 IN ('A', 'B') AND
table1.col3 IN ('A', 'B')
Is this expected behavior? It surprised me that the second
query would take full advantage of the index but not the
third. We're using MySQL 4.1.20.
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ __
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
