Reputation: 110592
I have two queries that are as follows:
SELECT * FROM table WHERE asset_type=%s AND country=%s AND series=%s
SELECT * FROM table WHERE asset_type=%s AND country=%s AND episode=%s
Should I add one composite index, including all four fields? Or two composite indexes, one for each query?
ALTER TABLE table ADD INDEX (asset_type, country, series)
ALTER TABLE table ADD INDEX (asset_type, country, episode)
-- or --
ALTER TABLE table ADD INDEX (asset_type, country, series, episode)
Why should I choose one over the other?
Upvotes: 0
Views: 53
Reputation: 1271241
If you want to maximally optimize both queries, then use two indexes.
If the asset_type
and country
fields are highly selective -- meaning that they select very few of the rows in the original table -- then the one index will work. Note that for the second query, the first two keys will be used to find a position in the index to start scanning.
Upvotes: 2