-none- 2007-10-03 - By Rob Wultsch
Back
It is way past bed time so excuse me if I am way off...
What is the order of tables in the explain? What is shown as the select_type?
On 10/2/07, Eamon Daly <edaly@(protected)> wrote:
> 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.
--
Rob Wultsch
(480)223-2566
wultsch@(protected) (email/google im)
wultsch (aim)
wultsch@(protected) (msn)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
