CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110592

Two composite indexes or one?

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions