MySQL Forums
Forum List  »  Optimizer & Parser

MyISAM using 'wrong' index
Posted by: Danny Stommen
Date: June 08, 2015 04:14AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
MyISAM using 'wrong' index
1667
June 08, 2015 04:14AM
944
June 09, 2015 11:53PM
1466
June 30, 2015 03:22AM
1125
July 02, 2015 09:28PM
933
July 03, 2015 03:16AM
975
July 04, 2015 11:10AM
917
July 07, 2015 01:56AM
860
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.