Maybe I'm wrong :)
On Tuesday, July 21, 2009, John Daisley john.dais...@mypostoffice.co.uk wrote:
On Tue, 2009-07-21 at 19:42 +0200, Morten Primdahl wrote:
On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote:
MySQL is unable to use your index when you use IN and/or OR on yoru
column.
MySQL is unable to use your index when you use IN and/or OR on yoru column.
If the query is slow, you should switch to a union:
SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND
status_id =1
UNION
SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND
status_id
Try doing a SHOW INDEX FROM orders and look at the cardinality
column. These are the stats MySQL uses to determine which index to
use. Sometimes they aren't always update properly and you may need to
run ANALYZE on the table.
But, you can also tell MySQL to use the index you want.
SELECT * FROM
On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote:
MySQL is unable to use your index when you use IN and/or OR on yoru
column.
Is this really true?
I'm reading High Performance MySQL 2nd ed. these days and
specifically got the impression that using IN will allow usage of the
index. The
The other index does have a way higher cardinality, but the query is
for 3 columns all of which are in the first index. I guess this is
just one of the situations where MySQL makes a wrong assessment.
On Jul 21, 2009, at 3:54 PM, Brent Baisley wrote:
Try doing a SHOW INDEX FROM orders
On Tue, 2009-07-21 at 19:42 +0200, Morten Primdahl wrote:
On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote:
MySQL is unable to use your index when you use IN and/or OR on yoru
column.
Is this really true?
No its not true! Try running OPTIMIZE TABLE on the affected table, then
run