CODEWITHSUNDEEP
mysqlsqlindexingquery-optimization
Deyan Vitanov
Deyan Vitanov

Reputation: 784

What SQL indexes to put for big table

I have two big tables from which I mostly select but complex queries with 2 joins are extremely slow.

First table is GameHistory in which I store records for every finished game (I have 15 games in separate table). Fields: id, date_end, game_id, ..

Second table is GameHistoryParticipants in which I store records for every player participated in certain game. Fields: player_id, history_id, is_winner

Query to get top players today is very slow (20+ seconds). Query: 

SELECT p.nickname, count(ghp.player_id) as num_games_today  
FROM `GameHistory` as gh 
INNER JOIN GameHistoryParticipants as ghp ON gh.id=ghp.history_id    

INNER JOIN Players as p ON p.id=ghp.player_id     

WHERE TIMESTAMPDIFF(DAY, gh.date_end, NOW())=0 AND gh.game_id='scrabble' 

GROUP BY ghp.player_id ORDER BY count(ghp.player_id) DESC LIMIT 10

First table has 1.5 million records and the second one 3.5 million. What indexes should I put ? (I tried some and it was all slow)

Upvotes: 0

Views: 99

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

You are only interested in today's records. However, you search the whole GameHistory table with TIMESTAMPDIFF to detect those records. Even if you have an index on that column, it cannot be used, due to the fact that you use a function on the field.

You should have an index on both fields game_id and date_end. Then ask for the date_end value directly:

WHERE gh.date_end >= DATE(NOW())
AND gh.date_end < DATE_ADD(DATE(NOW()), INTERVAL 1 DAY)
AND gh.game_id = 'scrabble'

It would even be better to have an index on date_end's date part rather then on the whole time carrying date_end. This is not possible in MySQL however. So consider adding another column trunc_date_end for the date part alone which you'd fill with a before-insert trigger. Then you'd have an index on trunc_date_end and game_id, which should help you find the desired records in no time.

WHERE gh.trunc_date_end = DATE(NOW())
AND gh.game_id = 'scrabble'

Upvotes: 1

Kelvin Barsana
Kelvin Barsana

Reputation: 864

add 'EXPLAIN' command at the beginning of your query then run it in a database viewer(ex: sqlyog) and you will see the details about the query, look for the 'rows' column and you will see different integer values. Now, index the table columns indicated in the EXPLAIN command result that contain large rows.

-i think my explanation is kinda messy, you can ask for clarification

Upvotes: 0

Related Questions