How can my SQL query be optimized?


Problem :

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.



Solution :

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.


Mysql Tutorials

Mysql Howto..

MySql - SQL - How to avoid duplicate updates in a single query

How to create a table from a select statement in MySQL preserving encoding and DB engine

How to retrieve items with hibernate clustered by to_days + COUNT(*)

MySql: How to update a single row occurrence from a table that has rows with duplicate fields?

How to merge 2 filed in mysql with 1 query

How to know the id (autoincrement) of the entry recently made to MySQL?

how to synchronize changes on mysql EER diagram with table

MySQL SELECT with JOIN - how to code correct

How to optimize this in MySQL?

How to make different categories view from one php file

How to fetch NULL values in Mysql through joins?

How do I mitigate duplicate row inserts based on a non-key column?

sql show all dates on count query

How to get count of distinct rows in MySQL?

How to fetch only certain rows from an MySql database table

How to insert into table 1 if not exists in table2?

Upgrading SQL Server 2000 to MySQL 5.1 using DTS - How solve DateTime problem?

How do I lookup two fields against same (foreign) table

How to browse every 10 records using MYSQL and jquery ajax calls

How to conver rich:calender date to mysql Date

How can I optimize or combine these MySQL queries?

How can i optimize this mysql query ? i use IN() operator

How to edit dynamically generated datatable and datagridview?

How to implement multiple MYSQL tables with foreign keys into HTML table with PHP?

How to save data from array (PHP) to MySQL table? (using MySQLi)

Using wamp server how can I have mysql directory on another location?

How to Grab value from ancestors in MySQL where child element is null recursively using PHP

How to delete a row from mysql via php

How to change dropdown selection into typeahead which using mysql database?

How to execute the code inside javascript confirm box