MySQL Forums
Forum List  »  Optimizer & Parser

Re: MyISAM using 'wrong' index
Posted by: Rick James
Date: June 09, 2015 11:53PM

The JOIN may be complicating the situation. First, does it really need to be "LEFT"?

Please provide SHOW CREATE TABLE and make it clear which fields are in which table.

It will take a while, but do ANALYZE TABLE on each table. Sometimes MyISAM's statistics get out of date and it picks the wrong index or JOIN order.

What percentage of the table has (date BETWEEN Y AND Z)? What percentage of those rows have campaign_id=X? If more than about 20%, it will decide (usually correctly) that it is better to ignore the index and simply do a table scan.

Is it really using the PK? If it were InnoDB, it would make some sense. Please provide the EXPLAIN.

How much RAM? SHOW VARIABLES LIKE '%buffer%';

Options: ReplyQuote


Subject
Views
Written By
Posted
1823
June 08, 2015 04:14AM
Re: MyISAM using 'wrong' index
1017
June 09, 2015 11:53PM
1578
June 30, 2015 03:22AM
1225
July 02, 2015 09:28PM
1044
July 03, 2015 03:16AM
1108
July 04, 2015 11:10AM
1029
July 07, 2015 01:56AM
930
July 07, 2015 09:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.