How can my SQL query be optimized?
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.
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.
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.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]