1

I have a database of observations. Each observation contains a station identifier among other things. In order to update our station list, I want to know which stations have observed more than 720 times in the past 30 days. But I noticed something funny. Just for kicks I was breaking it up by starting letter. For example,

SELECT station,COUNT(station) FROM observations WHERE station > "C" AND 
station < "D" GROUP BY station HAVING COUNT(station) > 720 LIMIT 100;

This query took 1.51 seconds to complete, returning 100 rows. Next I put

SELECT station,COUNT(station) FROM observations WHERE station > "C" AND 
station < "D" GROUP BY station HAVING COUNT(station) > 720 LIMIT 200;

This query took 5.58 seconds. Okay I thought it probably shouldn't take that long, but I'll live. Finally, I tried

SELECT station,COUNT(station) FROM observations WHERE station > "C" AND 
station < "D" GROUP BY station HAVING COUNT(station) > 720 LIMIT 300;

This took a whopping 4 minutes and 28 seconds to complete!

I wonder if anyone has a theoretical explanation as to why the counting appears to "get slower faster" as I up the limit on the query. Also, I am not a MySQL professional, I just use it to get things into my csv files so I can go after them with C++. Is there anything in my query itself that is causing problems here? Or is COUNT() just slow?

nonremovable
  • 798
  • 1
  • 6
  • 19
  • 1
    Have you seen this question/answer : http://stackoverflow.com/questions/511820/select-count-is-slow-even-with-where-clause – PaulF Aug 21 '15 at 13:06
  • Have you tried running the queries seperately and checking the behaviour, i.e run one query with station and another with count(station). When you do this, better, if you also, run a count(*) too. compare the timings in all 3 cases and share here. Will isolate the problem – Umashankar Das Aug 21 '15 at 13:45
  • When I erased COUNT(station) from the query (ie between the SELECT and FROM commands) on the last thing it ran in 5.55 seconds. – nonremovable Aug 21 '15 at 14:03
  • possibly unrelated, but if you use limit you should define the order of rows. – 1010 Aug 21 '15 at 17:31
  • 1
    please post the structure of your table and any indices on it. – 1010 Aug 21 '15 at 17:51
  • Please provide `EXPLAIN SELECT ...;` – Rick James Aug 21 '15 at 22:15

0 Answers0