CODEWITHSUNDEEP
mysqlindexing
Pablo
Pablo

Reputation: 75

Optimize slow SQL query using indexes

I have a problem optimizing a really slow SQL query. I think is an index problem, but I can´t find which index I have to apply.

This is the query:

SELECT
    cl.ID, cl.title, cl.text, cl.price, cl.URL, cl.ID AS ad_id, cl.cat_id,
    pix.file_name, area.area_name, qn.quarter_name
FROM classifieds cl
/*FORCE INDEX (date_created) */

INNER JOIN classifieds_pix pix ON cl.ID = pix.classified_id AND pix.picture_no = 0
INNER JOIN zip_codes zip ON cl.zip_id = zip.zip_id AND zip.area_id = 132
INNER JOIN area_names area ON zip.area_id = area.id
LEFT JOIN quarter_names qn ON zip.quarter_id = qn.id
WHERE
    cl.confirmed = 1
    AND cl.country = 'DE'
    AND cl.date_created <= NOW() - INTERVAL 1 DAY
ORDER BY
    cl.date_created
desc LIMIT 7

MySQL takes about 2 seconds to get the result, and start working in pix.picture_no, but if I force index to "date_created" the query goes much faster, and takes only 0.030 s. But the problem is that the "INNER JOIN zip_codes..." is not always in the query, and when is not, the forced index make the query slow again.

I've been thinking in make a solution by PHP conditions, but I would like to know what is the problem with indexes.

Upvotes: 0

Views: 810

Answers (3)

Johan
Johan

Reputation: 76537

Add explain in front of the query and run it again. This will show you the indexes that are being used.

See: 13.8.2 EXPLAIN Statement

And for an explanation of explain see MySQL Explain Explained. Or: Optimizing MySQL: Queries and Indexes

Upvotes: 0

Tomer Shay
Tomer Shay

Reputation: 801

These are several suggestions on how to optimize your query.

  1. NOW Function - You're using the NOW() function in your WHERE clause. Instead, I recommend to use a constant date / timestamp, to allow the value to be cached and optimized. Otherwise, the value of NOW() will be evaluated for each row in the WHERE clause. An alternative to a constant value in case you need a dynamic value, is to add the value from the application (for example calculate the current timestamp and inject it to the query as a constant in the application before executing the query. To test this recommendation before implementing this change, just replace NOW() with a constant timestamp and check for performance improvements.
  2. Indexes - in general, I would suggest adding an index the contains all columns of your WHERE clause, in this case: confirmed, country, date_created. Start with the column that will cut the amount of data the most and move forward from there. Make sure you adjust the WHERE clause to the same order of the index, otherwise the index won't be used.

I used EverSQL SQL Query Optimizer to get these recommendations (disclaimer: I'm a co-founder of EverSQL and humbly provide these suggestions).

Upvotes: 1

DRapp
DRapp

Reputation: 48129

I would actually have a compound index on all elements of your where such as

(country, confirmed, date_created)

Having the country first would keep your optimized index subset to one country first, then within that, those that are confirmed, and finally the date range itself. Don't query on just the date index alone. Since you are ordering by date, the index should be able to optimize it too.

Upvotes: 0

Related Questions