Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.

How to speed up queries on "qa_eventlog"?

+2 votes
84 views
asked Sep 17, 2017 in Q2A Core by q2apro

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! 

Q2A version: 1.7.4
commented Sep 18, 2017 by Scott
What page is the query run on?
commented Sep 18, 2017 by q2apro
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.

1 Answer

+2 votes
answered Sep 18, 2017 by pupi1985
selected Sep 19, 2017 by q2apro
 
Best answer

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. Create a compound index on datetime and event (in that order):

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

commented Sep 18, 2017 by Scott
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.
commented Sep 18, 2017 by pupi1985
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
commented Sep 19, 2017 by q2apro
Thanks for your great answer! Yes, it is the liveticker plugin: https://www.q2apro.com/plugins/liveticker - 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.
...