Long query execution - SSP Datatables

Question

I use Datables with SSP Class. And i have query with result 2000 lines. But when I try running query I got error 500/504 error (if I have 504 table is didn't load) I use OVH CloudDB with MariadDB 10.2 and on server I have php 7.2 My joinQuery looks like:

FROM data_platforms p
LEFT JOIN game_platforms gp ON gp.platform_id = p.platform_id
LEFT JOIN games g ON g.game_id = gp.game_id
LEFT JOIN tastings t ON t.tasting_game_id = g.game_id
LEFT JOIN notes n ON n.note_game_id = g.game_id
LEFT JOIN ratings r ON r.rating_game_id = g.game_id
LEFT JOIN images i ON i.image_type_id = g.game_id AND (i.image_type = 2 || i.image_type = 1)
LEFT JOIN game_generes gg ON gg.game_id = g.game_id
LEFT JOIN generes gen ON gen.id = gg.genere_id

And extraWhere

p.platform_id = '.$platformID.' AND g.game_status = 1

And groupBy

gp.game_id

Is there any way to be able to optimize this query?

Am I doomed to fail at this point, or should I use a different SSP class?

Answer 1

Don't use LEFT unless you really expect the 'right' table to be missing.

Don't "over-normalize". For example, I would expect genre to simply be a column, not a many-to-many mapping table plus a genre table.

(i.image_type = 2 || i.image_type = 1) --> i.image_type IN (1,2) might optimizer better.

See this for a likely improvement in many-to-many table indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Please provide SHOW CREATE TABLE so we can check other indexes, such as on platform_id.

Let's see the entire query, plus EXPLAIN SELECT .... GROUP BY gp.game_id may be invalid (unless everything else is dependent on it).