Optimize slow SQL query using indexes

Question

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.

I've tried what you told me and make a lot of research about the query without any success, finally I've used SOLR to make it faster. — Pablo
– Pablo, Commented Dec 9, 2011 at 11:09

Peter Mortensen · Accepted Answer · 2018-12-06 19:05:42Z

1

These are several suggestions on how to optimize your query.

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.
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).

edited Dec 6, 2018 at 19:05

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jul 2, 2017 at 8:20

Tomer Shay

8017 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

N.B. Over a year ago

You're making suggestions without seeing the execution plan at all. All of your points are basically useless for any kind of optimization. Whether NOW() is "evaluated" or not makes no noticeable difference. Indexing a column that has only 2 values deteriorates performance, cardinality of such column is horrible and all you can achieve is waste space. The problem is that OP will go through every row in his db, whatever he does. His "forced index" run is fast because query is already cached. Had he posted an explain plan, we'd see it clearly. This looks like advertisement to me.

Tomer Shay Over a year ago

I respectfully disagree. I saw several instances when implementing these two suggestions reduced the query execution time. An example from SO will be: stackoverflow.com/questions/44650725/speeding-up-order-by-date By the way, not providing any recommendations because there is no explain plan is worst than providing something that might work and might not, which depends on the explain plan (at least in my opinion). This way, the OP can try these recommendations and decide whether they are helpful or not.

N.B. Over a year ago

You can respectfully disagree with me all you like, but you can't disagree with facts. Since there are no sufficient facts in this particular question, all that's left is logic. By reading that query, I can assert that MySQL will go through all rows of a single table. To assert that index forcing does something, one would have to clear the query cache before repeating the query with index forcing. You have no info whether OP did that. I'm not even talking about ordering by.

DRapp · Accepted Answer · 2011-11-10 16:16:48Z

0

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.

answered Nov 10, 2011 at 16:16

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2023-01-17 20:34:07Z

0

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

edited Jan 17, 2023 at 20:34

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Nov 10, 2011 at 15:50

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Collectives™ on Stack Overflow

Optimize slow SQL query using indexes

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related