MYSQL Count slowing down query considerably

Question

I have a mysql query which returns rows within a given distance as below. The query runs in around 0.002 seconds which is fine :

SELECT round( sqrt( ( ( (adverts.latitude - '53.757729') * (adverts.latitude - '53.757729') ) * 69.1 * 69.1 ) + ( (adverts.longitude - '-2.70344') * (adverts.longitude - '-2.70344') * 53 * 53 ) ), 1 ) as distance 
  FROM adverts 
  WHERE (adverts.status = 1) 
  AND (adverts.approved = 1) 
  AND (adverts.type_id = 3) 
  AND (adverts.latitude BETWEEN 53.049320633786 AND 54.466137366214) 
  AND (adverts.longitude BETWEEN -3.9017500913496 AND -1.5051299086504) 
  having (distance <= 49)

When I try to return the count of the rows using the query below, the execution time increases dramatically to 0.024 seconds. Is there a faster way to get a count of the number of results.

SELECT count(*) from (SELECT round( sqrt( ( ( (adverts.latitude - '53.757729') * (adverts.latitude - '53.757729') ) * 69.1 * 69.1 ) + ( (adverts.longitude - '-2.70344') * (adverts.longitude - '-2.70344') * 53 * 53 ) ), 1 ) as distance 
  FROM adverts 
  WHERE (adverts.status = 1) 
  AND (adverts.approved = 1) 
  AND (adverts.type_id = 3) 
  AND (adverts.latitude BETWEEN 53.049320633786 AND 54.466137366214) 
  AND (adverts.longitude BETWEEN -3.9017500913496 AND -1.5051299086504) 
  having (distance <= 49)) as x;

Here is the explain on the secondquery which includes the count

1  PRIMARY  <derived2>  ALL   NULL  NULL  NULL  NULL  10352  
2  DERIVED  adverts     range r...  b..   11    NULL  10352  Using where; Using index

@e4c5 this is not an argument. add more data to a table, and you will get a satisfying number. Do more requests - whatever. the question is clear and fair. — Your Common Sense, Nov 15 '16 at 12:10
in that case @YourCommonSense this is premature optimizations because the indexes used (or not used) when there is a large amount of data in the tables can and will be different from the indexes used (or not used) now — e4c5, Nov 15 '16 at 12:12
@YourCommonSense that is also a good point because the primary bottleneck may well be the count on a derived table — e4c5, Nov 15 '16 at 12:21
Hi @e4c5, thanks for your response regarding the 0.024 seconds. I know its not that high, but our site has over 6000 concurrent users on at busy periods and this mysql query is run on one of the most popular web pages. So if I could reduce the query time to 0.002 or similar then this would be a great performance boost. — Mark, Nov 15 '16 at 12:29
have you looked at mysql spatial extensions? http://stackoverflow.com/a/38771805/267540 http://stackoverflow.com/a/38548557/267540 — e4c5, Nov 15 '16 at 12:32
@e4c5 this is again irrelevant. the question is how to avoid a temporary table, not how to calculate a distance. — Your Common Sense, Nov 15 '16 at 12:46
@Mark it would be a good start if you add EXPLAIN result for both queries. I am sure there is a temporary table involved, but it's better not to guess — Your Common Sense, Nov 15 '16 at 12:47
@YourCommonSense on the contrary, the calculation could very well be another bottleneck. But you are also guessing. — e4c5, Nov 15 '16 at 12:48
My guess is that `WHERE round( sqrt...etc.` will be fractionally faster — Strawberry, Nov 15 '16 at 12:51

score 0 · Answer 1 · answered Nov 15 '16 at 13:12

0

Assuming that the problem is coming from the temporary table to which the result of the first query is stored, I would suggest to change the query to avoid a nested select:

SELECT count(*)
  FROM adverts 
  WHERE (adverts.status = 1) 
  AND (adverts.approved = 1) 
  AND (adverts.type_id = 3) 
  AND (adverts.latitude BETWEEN 53.049320633786 AND 54.466137366214) 
  AND (adverts.longitude BETWEEN -3.9017500913496 AND -1.5051299086504) 
  AND (round( sqrt( ( ( (adverts.latitude - '53.757729') * (adverts.latitude - '53.757729') ) * 69.1 * 69.1 ) + ( (adverts.longitude - '-2.70344') * (adverts.longitude - '-2.70344') * 53 * 53 ) ), 1 ) <= 49);

answered Nov 15 '16 at 13:12

Your Common Sense

156,878
40
214
345

Thanks, I will give this a try. would it be quicker if I removed the two lines for the bounding box? – Mark Nov 15 '16 at 13:39
Well it may and it may not. You see, it's hardest job in the world, *profiling by email*. I know nothing of your database, data, environment - how can I tell? Nevertheless, I don't think that any tweaks for the original query are on topic here. Are you still concerned of the second query performance? – Your Common Sense Nov 15 '16 at 13:52

MYSQL Count slowing down query considerably

1 Answers1