How can my SQL query be optimized?

Question

I have a problem with a quite slowish MYSQL query. I'm building an AJAX menu with PHP and performance is really an issue. The query takes about 0,5 sec to complete, and i don't know how to optimize it.

The SQL query :

SELECT M.nom_mat, M.id_mat, M.rang, SC.nom_sous_cat, SC.id_sous_cat, C.nom_cat,M.id_cat
FROM besson_mat M
LEFT OUTER JOIN besson_lien_mat LM ON M.id_mat = LM.id_mat
LEFT OUTER JOIN besson_sous_cat SC ON SC.id_sous_cat = LM.id_sous_cat
LEFT OUTER JOIN besson_cat C ON C.id_cat = SC.id_cat
WHERE M.en_ligne = '1'
AND M.lg = 'fr'
AND (
M.id_cat = '28'
OR M.id_cat = '29'
)
OR (
SC.id_sous_cat = '37'
OR SC.id_sous_cat = '42'
OR SC.id_sous_cat = '43'
OR SC.id_sous_cat = '44'
)
ORDER BY C.id_cat ASC , SC.id_sous_cat ASC , M.rang ASC

Thanks for your help, I'll update my question if you need more details.

FINAL EDIT

The extra parentheses in the WHERE clause were the cause of the problem. Now my Query takes about 0.0718 sec to complete, thank you so much.

Answer 1

You could use the column IN(id1, id2, id3,...) syntax instead of that many ORs.

Second, Mysql has a EXPLAIN <statement> command which gives you some hints, what it does and how you could optimize your query (use index, redesign the joins etc) See http://dev.mysql.com/doc/refman/5.0/en/explain.html

Answer 2

Don't do Join only to filter, you can use exists or not exists clause, it's better.

Maybe remove your order by clause and do your sorting in your app.

and you can also ad some index in your id column, lg and en_ligne

Avec plaisir et bienvenu sur SO

Answer 3

Typically you might consider putting indexes on any of the columns used in your JOINS, WHERE clauses, and ORDER BY clauses.

If that doesn't help, use your database development interface to do an explain plan on the query to see where it's bogging down.

You might try putting the results of your Explain Plan here.