How to optimize this query in MySQL that is very slow?

Question

Navigation

#1 by (3 votes)

1

I'm doing a simple query in LMS Moodle to get the amount of access from all users.

SELECT
    count(userid) as total,
    action,
    userid
FROM
    mdl_logstore_standard_log log
WHERE
    action = 'loggedin'
GROUP BY 
    userid

The table involved has more than 500 thousand lines and the trend is only to increase.

I ran it 4, 5 minutes ago and still did not show the results.

Is there a way to do some optimization?

My knowledge is limited in Database Performance.

mysql moodle

asked by anonymous 11.09.2017 / 16:39

1 answer

score 3 · Accepted Answer

Add a INDEX to your table. Since the filter field is action , its INDEX should be referenced in this same field:

ALTER TABLE mdl_logstore_standard_log ADD INDEX IAction (action);

If you'd like to deepen your knowledge with INDEX of MySQL , click here .