[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: what kind of indices to set up
From:       Baron Schwartz <baron () xaprb ! com>
Date:       2007-04-26 16:05:05
Message-ID: 4630CDB1.5040702 () xaprb ! com
[Download RAW message or body]

Gerald L. Clark wrote:
> James Tu wrote:
>> I was a little to quick with the send button.
>>
>> Can you do a query like this:
>>
>> (I know that the * syntax is not correct, but is there something  
>> equivalent to it?
>>
>> SELECT from cars
>> WHERE
>>     make=5 AND
>>     model=* AND
>>     body_color=7 AND
>>     tire_type = * AND
>>     hub_caps_type = 1
>>
>>
>> If you could perform a query like the one above, would MySQL still  
>> use the multi-column index that I set up?
>>
>> -James
>>
> Select * from cars
> WHERE
>     make=5 AND
>     model LIKE '%' AND
>     body_color=7 AND
>     tyre_type LIKE '%' AND
>     hub_caps_type = 1
> 
> If your multi-column index starts with model or tyre_type, then no.
> The index can be used down to, but not including the first column that 
> is compared against '%'.

I think, but am not sure, that MySQL query plan optimizer will in fact 
remove the '%' condition entirely, if the column is defined as NOT NULL. 
  It is a tautology after all.  Test with EXPLAIN EXTENDED followed by 
SHOW WARNINGS to see the optimized query on MySQL 5 and up.

Baron

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql-marcsub@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]