MyISAM using 'wrong' index

[Danny Stommen]

Hi all,

I'm wondering how MySQL decides which index to use.

Some facts about the table:
- Engine: MyISAM with 25 columns
- ~14 million records
- It contains 60 days of data.
- It has a PK on (date, A, B, .., campaign_id)
- It has a secondary index on (campaign_id, date)

Doing the following query:
SELECT
..
FROM report_table
LEFT JOIN reference_table ON ..
WHERE campaign_id = X
AND date BETWEEN Y AND Z
GROUP BY ..;

where Y and Z is a 7 day date range

it will use the PK index, instead of the secondary index. Using PK the query takes 5.5sec to complete. When I force it to use the secondary index, it completes within 0.4 sec.

In the given date range are 282 distinct campaign_id values.


Any idea why MySQL decides to use the PK?