CODEWITHSUNDEEP
mysql
IT_Guy
IT_Guy

Reputation: 223

Simple SQL query taking a long time

I have a query that I use to determine what the interval is between timestamps of gauge data using the 2 most recent readings:

 $interval_query    = sprintf("SELECT `stamp` 
                              FROM `processed_gauge_data`
                              WHERE `processed_gauge_data`.`gauge_id` IN (%s) 
                              ORDER BY `processed_gauge_data`.`stamp` DESC LIMIT 2;",
                      $gauge_id
                  );

Here is an image with EXPLAIN results as well as the structure of the table: https://i.sstatic.net/WaDRP.png

This has worked fine for most gauges, but there are 2 in particular that it takes 30-45 seconds to execute this query. Selecting all data for those 2 gauges takes less than a second. What is causing this? I don't understand what's going on.

Upvotes: 1

Views: 45

Answers (1)

IT_Guy
IT_Guy

Reputation: 223

Turns out it was because of ORDER BY processed_gauge_data.stamp DESC. I changed my query to ORDER BY 'id' and it went from 30-45 seconds to .0006-.0003 seconds:

 $interval_query    = sprintf("SELECT `stamp`, 'id' 
                          FROM `processed_gauge_data`
                          WHERE `processed_gauge_data`.`gauge_id` IN (%s) 
                          ORDER BY `processed_gauge_data`.`id` DESC LIMIT 2;",
                  $gauge_id
              );

Upvotes: 1

Related Questions