How to optimize this query in Mysql that is too slow?

Asked

Viewed 1,054 times

1

I’m doing a simple query on LMS Moodle to get the amount of hits 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 it still hasn’t shown the results.

There is no way to do any optimization?

My knowledge is limited in Database Performance.

mysql optimizing moodle
  • 1

    Add an index with the field action in this way: ALTER TABLE mdl_logstore_standard_log ADD INDEX IAction (action);

  • 1

    Put a EXPLAIN and tell us what the return is.

  • Returns column names, types...

  • Only shows auto_increment in the primary key. In the rest of the columns it is empty.

  • @Robertofagundes worked the Index.

  • Okay, I’ll put it in answer.

Show 1 more comment

1 answer

3


Add a INDEX in your table. As the filter field is the action, your INDEX reference to the same field:

ALTER TABLE mdl_logstore_standard_log ADD INDEX IAction (action);

If you want to deepen the knowledge with the INDEX of MySQL, click here.

Browser other questions tagged mysql optimizing moodle

You are not signed in. Login or sign up in order to post.