2

I have the following query to select users and their locations, etc from MySQL (all InnoDB). The table user, blocked_user and blocked_countries have about 2mil rows each, countries about 250, regions about 3500 and cities about 2.8mil.

The Problem is that the query is quite fast (0.05sec) without AND co.country_id='us' in the WHERE clause. However, I tried around so much but just have the feeling that there must be certainly a much easier and better way for this query, no?

What am I missing? Any help is highly appreciated! Thank you very much in advance!

Improve this question
17
  • and how slow is it with the contry_id within the where clause? Commented Jun 10, 2012 at 13:34
  • @Sebas: I haven't figured out yet, as after some minutes I stopped the operation each time... Commented Jun 10, 2012 at 13:35
  • ok, one question: why are you using left joins? Do you know that by adding an equality in the where clause you transform a left join into an inner join? your statement about co.country_id = 'us' is affecting your left join with country table Commented Jun 10, 2012 at 13:38
  • anyway we'll need a real extract of the table structure I guess Commented Jun 10, 2012 at 13:40
  • @Sebas: really?? No, I did not know that! :O Oh, thank you very much for this, it explains a little bit now... OK, thank you I'll post the table structure. Commented Jun 10, 2012 at 13:43

2 Answers 2

Reset to default
2

Thank you again for your help. Finally, I've found the direction where to solve the problem. The performance problem was mainly caused because of a mixture between a range scan and an ORDER BY ... LIMIT.

So, I've learned a lot (new to me) about indexing and found that if you have a range scan (as in my case age between x and y), then the ranging column must be the last in a concatenated index, otherwise the index cannot be used entirely. In addition, the column used to sort the results, must the last in the index. So, given a mixture between range scan and ORDER BY, you need to decide if the index should be used to sort or to select.

In my case it means thinking of some good combinations for several indexes, where the left over rows to be sorted are just a very few or if the search cannot be narrowed down to just some rows, a separate index that will be used for sorting as the query will then quickly find the number of rows as given by LIMIT.

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

Comments

1

Try changing the WHERE to HAVING for the country_id

AND birthdate <=  '802726152'
AND birthdate >=  '676495752'

ORDER BY u.id DESC
HAVING co.country_id='us'
LIMIT 20
Improve this answer

5 Comments

The HAVING clause is for comparing values which were being aggregated in some sort of way, not adding in additional filtering.
I trust that you've tested this before downvoting my post? I would suggest doing so. MySQL's website would confirm the following "The WHERE clause is used to restrict records, and is also used by the query optimizer to determine which indexes and tables to use. HAVING is a "filter" on the final resultset, and is applied after ORDER BY and GROUP BY, so mysql cannot use it to optimize the query." The reason why I didn't need it to optimize the query, is because OP said it was already running fast without the country, so by using HAVING after the fact, he maintains his speed
@BryanMoyles: thank you Bryan for your help. Indeed, it was faster with using HAVING, probably because MySQL would not use country_id as a key, as you mentioned... it still takes some seconds, though.. but thank you again! :)
You're very welcome, I'm glad that it helped solve your problem :) Would you consider accepting my answer if it did indeed solve the problem to the best that it could be solved for the time being?
Hi Bryan, well to be honest it did not really help solving the problem. For that query it was faster, however still took between 6 and 45sec, which still is not acceptable for requesting data for 20 users on a search page as you have to admit... so I am still left alone with what I can improve. I managed to change the LEFT JOINS to INNER JOINS as judda suggested in his comments, so things slowly start to speed up. I appreciated your help, though...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.