SELECT * FROM tblactivitylog ...

[polyglot2]

I'm always seeing those queries whenever running "mysqladmin processlist". This query each takes around 8-12s on our server, and these seem to be slowing things down. What is the purpose of these queries? The LIMIT values seem weird.

[laszlof]

My guess is you have your "Limit Activity Log" set extremely high. (Settings -> General Settings)

[polyglot2]

I see, but wouldn't "SELECT COUNT(*) FROM tblactivitylog" be much faster (on a MyISAM table anyway)? And what's the second 99999 supposed to do?

 

And perhaps the checking can be done less often (with the consequence of the limit being overrun for a few records, which is not that big of a deal).

[laszlof]

It likely runs it everytime you view the activity log, which displays on the index of the admin.

 

COUNT(*) would not do the same thing. (and takes very little time to run)

 

The purpose of the query is to populate the tables for the admin activity log. We have the limit set to 10000 and do not see this issue.

[polyglot2]

Could you elaborate on this: "The purpose of the query is to populate the tables for the admin activity log." How does a SELECT query populate the (database) table? And why do you need to populate it?

[laszlof]

Could you elaborate on this: "The purpose of the query is to populate the tables for the admin activity log." How does a SELECT query populate the (database) table? And why do you need to populate it?

 

the "table" i'm referring to would be the "html table" on the index page of the admin that contains the activity log. Every time someone refresh the index page, it will run that query.

[polyglot2]

the "table" i'm referring to would be the "html table" on the index page of the admin that contains the activity log. Every time someone refresh the index page, it will run that query.

 

Still, what you're saying doesn't make sense. What's the point of doing a SELECT ... LIMIT <maxlog>, 99999 to fill some HTML table that's going to displaying a certain page (say 100 rows) only? To know the total number of rows? That's what SELECT COUNT(*) is for.

 

Also, sorry for my ignorance, do you have access to WHMCS source code?

[laszlof]

SELECT COUNT(*) will only return the number of rows. the LIMIT statement will return a specific number of rows, starting from a certain index point. Its probable that the query you're seeing is from someone viewing the activity log for a specific period.

 

No, I dont not have access to the WHMCS source code, its just an understanding of the query being ran, and what might need to run such commands within whmcs. WHMCS will not just run SELECT commands randomly unless needed. The only thing that would run a SELECT command on tblactivitylog would be the activity log located on the index page, or the one located in the utilities menu.

 

The solution is simple, reduce the number of activity log records you're keeping. We set ours to 10000, which gives us roughly 5 days worth of logs to view. Your results may vary depending on how many clients you have.