I have a scenario, where I have though optimized the query, but don’t understand on what basis the query optimizer choses to use a particular index. I am running the following query.
SELECT r.review_id FROM game_reviews r
INNER JOIN game_subchannel gs ON r.game_id = gs.game_id
WHERE gs.subchannel_id=4My table game_subchannel is a relationship table that maps game_id to subchannel_id. Initially I had a multiple column index game_subchannel_idx on game_id and subchannel_id columns. The index cardinality for both these columns is 4547, i.e. the number of rows in the table. When I did EXPLAIN on the query with only game_subchannel_idx in place, it would show 3411 rows and using where, using index in the Extra column, which as I have read means that it’s doing a full index scan & and not utilizing the index as I would ideally like it to.
My question here for the first case is that why is MySQL not using the game_subchannel_idx to single out rows based on the given condition?, i.e. why looking at 3411 rows? My understanding is that it should have a narrow set of rows to look at. And hence why is it doing a full index scan?
Later on what I did is create a new single column index subchannel_idx only on the subchannel_id column. The index cardinality here is 32. Now when I do EXPLAIN on this query, it shows both index in ‘possible keys’ column and subchannel_idx in key column.
And 645 in the rows column. And the Extra column is empty. So apparently I have now optimized the query, i.e. 3411 vs 645. But I don’t understand the process, there’s a lot of confusion. My question for the second case is why does MySQL prefer subchannel_idx over game_subchannel_idx, even though the former has a very low cardinality? What I have read is that MySQL usually prefers indexes with higher cardinality.
,
For MySQL >5.0. http://dev.mysql.com/doc/refman/5.0/en/index-hints.html