Optimizing MySQL query with multiple left joins

• Make sure that you have indexes on the fields that are in your WHERE statements and ON conditions, primary keys are indexed by default but you can also create indexes manually if you have to.

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_type]

index_col_name: col_name [(length)] [ASC | DESC]

index_type: USING {BTREE | HASH}

• Check if you really have to select every column in all of the tables? If not, make sure that you only select the columns that you need, avoid using select*

• Double check if you really need LEFT JOINS, if no, use INNER JOINs.

• If performance is still an issue after you're done tweaking your query, consider denormalizing your schema to eliminate joins

• You may also want to consider reducing the load on the database by using caching applications like sphinxsearch and memcached

• Check none of your joins are to views rather than actual tables

references:

http://www.sphinxsearch.com

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Make sure your tables are properly indexed. You need to have an index for every column you use to to select data, otherwise MySQL will go through every row in the table.

Try explaining the query (EXPLAIN SELECT * FROM ...etc) in the MySQL console to see how MySQL treats the tables internally. Paste the results into your question for additional help.