Query taking too long and not using index

Question

We have an events service which returns events filtered by id and event timestamp and ordered by primary column.

There is about 1.5 GB of data in this table

query:

SELECT event.eventID, event.orgID, event.objectType, event.action, event.objectID, 
       event.logEventID, event.eventTimestamp, event.userID, event.source, 
       event.additionalDetails, event.insertByUserID, event.insertDateTime, 
       event.modifyByUserID, event.modifyDateTime 
  FROM event 
 WHERE event.orgID = 100 
   AND event.eventTimestamp >= 1535046151000 
ORDER BY event.eventID ASC limit 10001;

Above query takes 14 seconds to execute.

If I remove ORDER BY event.eventID ASC, it takes 0.01 sec

Current index is on primary column idx1(eventID). We added a second index idx2(orgID,eventTimestamp) and still don't see performance improvement.

The query does not use the new index unless specified with "USE Hint". It takes 7 seconds with use hint and providing idx2.

We are on mysql version 5.6

Any thoughts on improving execution time?

Answer 1

The big suck is the Using filesort operation, we should see if we can get the rows returned in "index order" to avoid that operation.

I'd be tempted to add an index:

... ON `event` (`orgid`,`eventid`,`eventtimestamp`)

I'd also experiment with tweaking the query. While not strictly necessary, we could include the orgid column in the ORDER BY clause, since the conditions in the WHERE clause guarantee us a single value.

 ORDER BY event.orgid ASC, event.eventid ASC

The intent here to give the optimizer as much information as possible, that there's a suitable index for satisfying the ORDER BY clause.

Use EXPLAIN to see the execution plan.

We are trying to get MySQL to use an index range scan on orgid to return rows in "index" order by eventid. And then discard rows that don't satisfy the condition on eventtimestamp.

SELECT event.eventid
     , event.orgid
     , event.objecttype
     , event.action
     , event.objectid
     , event.logeventid
     , event.eventtimestamp
     , event.userid
     , event.source
     , event.additionaldetails
     , event.insertbyuserid
     , event.insertdatetime
     , event.modifybyuserid
     , event.modifydatetime
  FROM event
 WHERE event.orgid = 100
   AND event.eventtimestamp >= 1535046151000
 ORDER
    BY event.orgid ASC 
     , event.eventid ASC
 LIMIT 10001

If that's not sufficient to avoid the "Using filesort" operation, then we could try moving the condition on eventtimestamp out of the WHERE clause into a HAVING clause. (Replace the AND keyword with HAVING.)

---

An index omitting the eventtimestamp might be sufficient to get a reasonable execution plan.

instead of

... ON `event` (`orgid`,`eventid`,`eventtimestamp`)

this might work just as well

... ON `event` (`orgid`,`eventid`)