-none- 2007-10-03 - By Eamon Daly
Back
After spending half the night trying this same query on a
number of different datasets, it looks like sometimes MySQL
/will/ use all parts in certain cases, so I'm satisfied by
that. Thanks for responding!
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ __
Eamon Daly
-- -- Original Message -- --
From: "Rob Wultsch" <wultsch@(protected)>
To: <mysql@(protected)>
Sent: Wednesday, October 03, 2007 1:30 AM
Subject: Re: Multipart + IN comparison on the second part + JOIN does not
use full index
: 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)
|
|
