Re: MyISAM using 'wrong' index

[Rick James]

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%';