CODEWITHSUNDEEP
mysqlselectindexing
Tyler DeWitt
Tyler DeWitt

Reputation: 23576

Why do these 2 (similar?) MySQL queries vary so much in their execution times?

I have the tables players, players_to_teams, and accounts. players_to_teams has a FK to players and accounts. players has a FirstName and accounts has a Name. Both FirstName and Name are indexed.

Running 

SELECT players_to_teams.id
FROM players_to_teams
INNER JOIN players
ON players.id = players_to_teams.player_id
ORDER BY players.FirstName

executes in 0.004 seconds.

SELECT players_to_teams.id
FROM players_to_teams
INNER JOIN accounts
ON accounts.id = players_to_teams.account_id
ORDER BY accounts.Name

executes in over 7 seconds.

There are 30,000 account records and 250k player records.

EXPLAINs can be found here: https://gist.github.com/2368906

Shouldn't these queries execute in similar time spaces?

Upvotes: 2

Views: 119

Answers (2)

ESG
ESG

Reputation: 9425

Your slower query needs a temporary table to sort the result set, while the other one can use the index on FirstName, avoiding the sorting all together. (Extra: Using index; Using temporary; Using filesort)

Can you try forcing the index on accounts to see if you can outsmart the optimized?

SELECT players_to_teams.id
FROM players_to_teams 
INNER JOIN accounts FORCE INDEX(Name)
ON accounts.id = players_to_teams.account_id
ORDER BY accounts.Name

Another alternative would be to rewrite the query with a straight join, and have the accounts table listed first.

I don't have any good answer as to why it currently picks players_to_teams first when accounts has way less rows.

Upvotes: 1

DRapp
DRapp

Reputation: 48129

One other option that might help... that I remember reading somewhere... If you have an index on both the account ID and Name as a single index (id, name), then the engine does not have to go back to the raw data records to extract the name value and can pull it directly from the index... thus saving time from a full record read on the matches.

Upvotes: 1

Related Questions