-none- 2007-10-03 - By Baron Schwartz
Back
That probably means the optimizer is rejecting (part of) the index as
not selective enough to be efficient for the given query, depending on
storage engine index statistics.
Making sure your indexes are up to date can help on certain storage
engines (MyISAM). ANALYZE TABLE does this for you.
Eamon Daly wrote:
> 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)
>
>
--
Baron Schwartz
Xaprb LLC
http://www.xaprb.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
