Skip to content

Count query not using indexes

When a summary query is received, internally the webservice first performs a count query to check the results will not exceed the 20,000 limit. This count query is not using indexes and should be optimized.

Example Query

SELECT
  COUNT(*)
FROM (
  SELECT
    DISTINCT ps.*
  FROM
    event e
    JOIN eventSummary es ON (es.eventid = e.id)
    JOIN productSummaryEventStatus pses ON (pses.eventId = e.id)
    JOIN productSummary ps ON (ps.id = pses.productSummaryId)
    JOIN originSummary os ON (os.productid = ps.id)
  WHERE
    UPPER(e.status) <> 'DELETE'
    AND pses.eventPreferred = 1
    AND es.lastModified >= '1681761602000'
    AND e.eventTime >= '1679173202000'
  ORDER BY
    e.eventTime DESC
) x;

The corresponding explain plan is as follows:

+----+-------------+------------+------------+--------+---------------------------------------------+----------------------------+---------+-------------------------------+------+----------+---------------------------------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                               | key                        | key_len | ref                           | rows | filtered | Extra                                                               |
+----+-------------+------------+------------+--------+---------------------------------------------+----------------------------+---------+-------------------------------+------+----------+---------------------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                                        | NULL                       | NULL    | NULL                          |    2 |   100.00 | NULL                                                                |
|  2 | DERIVED     | es         | NULL       | range  | eventid,lastmodified                        | lastmodified               | 9       | NULL                          |    1 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |
|  2 | DERIVED     | e          | NULL       | eq_ref | PRIMARY,event_pointtime_index               | PRIMARY                    | 8       | indexer.es.eventid            |    1 |     5.00 | Using where                                                         |
|  2 | DERIVED     | pses       | NULL       | ref    | preferredEventProductIndex,productSummaryId | preferredEventProductIndex | 13      | indexer.es.eventid,const      |    1 |   100.00 | NULL                                                                |
|  2 | DERIVED     | os         | NULL       | eq_ref | productid                                   | productid                  | 9       | indexer.pses.productSummaryId |    1 |   100.00 | Using index                                                         |
|  2 | DERIVED     | ps         | NULL       | eq_ref | PRIMARY                                     | PRIMARY                    | 8       | indexer.pses.productSummaryId |    1 |   100.00 | NULL                                                                |
+----+-------------+------------+------------+--------+---------------------------------------------+----------------------------+---------+-------------------------------+------+----------+---------------------------------------------------------------------+

The resulting query doesn't typically take too long, but could be optimized by using indexes more effectively. Consider re-writing query, less ideal is to add a new index to the schema.

Acceptance Criteria

  • Query uses indexes