How to speed up queries on "qa_eventlog"?

Question

From my server logs, the slow notifier: 

# Time: 2017-09-17T19:48:06.840513Z

# Query_time: 8.226348  Lock_time: 0.000102 Rows_sent: 1  Rows_examined: 435073

SET timestamp=1505677686;

SELECT datetime,ipaddress,handle,event,params 
FROM `qa_eventlog`
WHERE UNIX_TIMESTAMP(datetime) > 1505677527
AND (`event`='q_post' OR `event`='a_post' OR `event`='c_post' OR `event`='a_select')
ORDER BY datetime DESC
LIMIT 10;

Is there any ways how I can speed up the table or to optimize the query? 8.2 seconds is huge! 

Comments

What page is the query run on?

---

I used to have this public. So on all question pages and /questions list.

What I did now: I copied "qa_eventlog" with those 435k entries to a newly created table "qa_eventlog_archive", then emptied "qa_eventlog", and of course it runs much faster again.

Answer 1

This table shouldn't be queried frequently. I mean, no user request should fire this query. The table is there just for backoffice purposes. E.G.: maybe you want to analyze some specific user behavior manually.

If you need something from it then create a filter module and log the information in your own (properly indexed) tables. There shouldn't be anything in the ^eventlog table that you can't get from a filter module.

IMPORTANT: don't be tempted to add an index on this table, which I guess it is what you're asking in your question. It will decrease the overall site performance.

Tips for your specific query:

1. Avoid the UNIX_TIMESTAMP function (see the example)

2. Use an IN instead of the ORs

3. Use a different table to store the events and create a compound index on datetime and event (in that order):

SELECT datetime,ipaddress,handle,event,params  
FROM `qa_custom_eventlog` 
WHERE datetime > CURDATE() - INTERVAL 5 DAY
AND event IN ('q_post', 'a_post', 'c_post', 'a_select') 
ORDER BY datetime DESC 
LIMIT 10

Comments

So I guess this is the fault of a plugin querying qa_eventlog? I can't see anywhere in Q2A that does a SELECT on it.

---

RIght. I think that's what he meant by "I used to have this public. So on all question pages and /questions list".

Anyway, I just took a look at the eventlog table and it actually has a couple of indexes. Still not the right one for the query he's running

---

Thanks for your great answer! Yes, it is the liveticker plugin. And yes, I thought I need to have an event listener and when an event is fired update the cached data. That's the way to go. I did the same with the best-users-pro plugin, totally speeded up everything. And thanks for the query tips.

---

"You are correct that using a function on the date column prevents MySQL from utilizing the index on the column." ... https://stackoverflow.com/a/11568797/1066234

Also use "FROM_UNIXTIME", see https://stackoverflow.com/a/33636636/1066234