Filter query timing out

[genghisken]

Matt raised a problem on the Community forum indicating that he was getting emails because of a filter query timeout.

[genghisken]

Roy looked into the logs. The query has an injected timeout setting hard wired to 10 seconds and some of Matt (Nicholl)'s NEEDLE queries were taking 9.5+ seconds, so very close to the limit. A closer look at the explain plan of Matt's query indicated that the code was doing a full table scan of all 1/4 million entries in the annotations table. Adding an index to the topic column immediately reduced the annotations row count to the 600 entries pertaining to NEEDLE.

Solution was to add an index to the topic column of annotations. This was rolled out as an emergency patch.

A subsequent check of the logs indicate that the NEEDLE query execution time had reduced from 9.5 seconds to 0.1 second.

Note - for future reference, the explain plan also indicated a full table scan being done on the 130,000 rows of the crossmatch_tns table. This hasn't yet been addresses, but we should take another look to see if the query could be further improved by adding another index.

For reference, the offending query is here:

SELECT objects.objectId,        
       crossmatch_tns.tns_name AS tns_name,
       objects.objectId,
       crossmatch_tns.type AS tns_class,
       round(jdnow() - objects.jdmin, 1) AS days_disc,
       round(jdnow() - objects.jdmax, 1) AS days_latest,
       if(objects.jdgmax > objects.jdrmax, objects.gmag, objects.rmag) mag_latest,
       if(objects.jdgmax > objects.jdrmax, "g", "r") band,
       if(objects.jdgmax > objects.jdrmax, objects.gmag - objects.maggmin, objects.rmag - objects.magrmin) mags_faded,
       ROUND(objects.g_minus_r, 2) AS g_minus_r,
       round(jdnow() - objects.jd_g_minus_r, 1) AS days_colour,
       objects.sgscore1 AS star_galaxy,
       sherlock_classifications.classification AS sherlock 
  FROM objects,
       sherlock_classifications, 
       watchlist_hits, 
       crossmatch_tns, 
       annotations AS needle
 WHERE objects.objectId=sherlock_classifications.objectId   
   AND objects.objectId=watchlist_hits.objectId   
   AND watchlist_hits.wl_id=1   
   AND watchlist_hits.name=crossmatch_tns.tns_name   
   AND objects.objectId=needle.objectId   
   AND needle.topic="needle"   
   AND objects.jdmax > jdnow()-15   
   AND NEEDLE.classification = "SLSN-I"   
   AND (objects.rmag < 20.0 OR objects.gmag < 20.0);

[RoyWilliams]

Added index to annotations table