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/[EMAIL PROTECTED]
