CODEWITHSUNDEEP
mysqlsqlmariadb
hedgehog90
hedgehog90

Reputation: 1501

Slow MYSQL Queries, need help understanding indexes

For the purposes of this post I've simplified my problem down to it's purest form. I have 3 tables: games, games_tags and games_tags_map

Here's what I do if I want to get a table of tags for each game:

SELECT `games_tags_map`.`game_id` as 'game_id', GROUP_CONCAT(`games_tags_map`.`tag_id`) as 'tags'
FROM `games_tags_map`
LEFT JOIN `games_tags` on `games_tags`.id = `games_tags_map`.`tag_id`
GROUP BY `games_tags_map`.game_id

It takes ~1ms

SELECT `games`.`id` AS 'id' from `games`

This takes <1ms.

However, when I try and join both of these:

SELECT `games`.`id` AS 'id',
t.`tags` as `tags`
FROM `games`
LEFT JOIN (
    SELECT `games_tags_map`.`game_id` as 'game_id', GROUP_CONCAT(`games_tags_map`.`tag_id`) as 'tags'
    FROM `games_tags_map`
    LEFT JOIN `games_tags` on `games_tags`.id = `games_tags_map`.`tag_id`
    GROUP BY `games_tags_map`.game_id
) t ON t.`game_id`=`games`.`id`

It takes ~100ms

However, when I do the equivalent query:

SELECT `games`.`id` AS 'id',
GROUP_CONCAT(DISTINCT `games_tags`.`tag`) AS 'tags'
FROM `games`
LEFT JOIN `games_tags_map` ON `games`.`id` = `games_tags_map`.`game_id`
LEFT JOIN `games_tags` ON `games_tags`.`id` = `games_tags_map`.`tag_id`
WHERE `games`.`active`=1
GROUP BY `games`.`id`

It takes 2ms... But, when I need to order it by anything other than the primary column (id), it takes ~80ms

Just to clarify, this is a very simplfied version of my actual database which is experiencing much longer loading times and causing problems for my website, but it's in queries like these where the problem lies.

There is clearly a flaw in the way my database is set up for such hugely different loading times. I've tried adding more indexes but it hasn't helped. On the table 'games' I have the primary index 'id' On the table 'games_authors_map' a primary index consisting of 'game_id' and 'author_id'

I know there's a problem but I can't fix it and I don't understand why.

Please help.

Upvotes: 2

Views: 62

Answers (2)

DRapp
DRapp

Reputation: 48139

Instead of doing a join to all the game tags table (which in itself is ok), why not add an aggregate column to you main games table with all the tags up front so you don't need to join. Then, you could simply add a trigger that whenever a tag is added or deleted from the game_tags_map table, it updates the main games table. If this is for purposes of just display to a web-based game site, you are good. If a person in interested in a game that is a certain type, then a query against the game_tags_map table would be good to summarize a list of that particular interest.

You are also doing a query of ALL games every time, so this may be the better avenue for you.

First, looking at your first query, and removing the ticks, quotes and aliasing your long table names to gtm and gt respectively, your query never even USES the games_tags table as it is a left-join and does not utilize any columns from it...

SELECT 
      gtm.game_id, 
      GROUP_CONCAT(gtm.tag_id) as tags
   FROM 
      games_tags_map gtm
         LEFT JOIN games_tags gt 
            on gtm.tag_id = gt.id
   GROUP BY 
      gtm.game_id

so in essence, it is doing nothing more than

SELECT 
      gtm.game_id, 
      GROUP_CONCAT(gtm.tag_id) as tags
   FROM 
      games_tags_map gtm
   GROUP BY 
      gtm.game_id

UNLESS, you intended to group_concat() to show the literal descriptions that the IDs represented instead of the IDs themselves. If by IDs, then your second query could ALSO remove the inner left-join for games_tags table.

SELECT 
      g.id AS id,
      t.tags as tags
   FROM 
      games g
         LEFT JOIN ( SELECT 
                          gtm.game_id, 
                          GROUP_CONCAT(gtm.tag_id) as tags
                       FROM 
                          games_tags_map gtm
                             LEFT JOIN games_tags 
                                on gtm.tag_id = gt.id
                       GROUP BY gtm.game_id ) t 
            ON g.id = t.game_id

In your last query, you are left-joining to actually get the TAG DESCRIPTIONS instead of the tags.

SELECT 
      g.id,
      GROUP_CONCAT(DISTINCT gt.tag) AS tags
   FROM 
      games g
         LEFT JOIN games_tags_map gtm 
            ON g.id = gtm.game_id
            LEFT JOIN games_tags gt
               ON gtm.tag_id = gt.id 
   WHERE 
      g.active = 1
   GROUP BY 
      g.id

To optimize this query, I would offer the following indexes..
This would make the entire query used with covering indexes and can process the entire query by indexes and never need to go to the raw underlying data.

table           index
games           ( active, id )
games_tags_map  ( game_id, tag_id )
games_tags      ( id, tag )

One final note, when trying to offer more detail to posts, you can always EDIT your existing post, add more detail, then send comment to the user regarding the additional data provided to review and possibly offer additional content / answer / response.

Upvotes: 2

Dinesh Singh
Dinesh Singh

Reputation: 104

Try to use index on foreign keys in the the tables ( games_tags_map.tag_id, games_tags_map.game_id) and the also index the column from which you trying to sort the query That will resolve your issue.

Upvotes: 0

Related Questions