Performance - slow mysql queries
mrfelton - June 7, 2009 - 13:21
| Project: | Statistics Advanced Settings |
| Version: | 6.x-1.5 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Description
Looking into my MySQL slow query log, I see numerous entries by statistics_advanced_settings, like these:
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/1685' OR uid > 0) AND sid = 's3s6hvh2vriegq5ndjn1g6e4h7' ORDER BY timestamp DESC LIMIT 0, 1;
# Time: 090607 14:14:04
# User@Host: concern[concern] @ [192.168.1.90]
# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 134675
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/3671' OR uid > 0) AND sid = 'v6ck4gu98dv5v8uqcqdlisarq0' ORDER BY timestamp DESC LIMIT 0, 1;
# Time: 090607 14:14:07
# User@Host: concern[concern] @ [192.168.1.90]
# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 134675
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/1597' OR uid > 0) AND sid = 'm9j5gigep7li4v2tnsskgdqt15' ORDER BY timestamp DESC LIMIT 0, 1;
# Time: 090607 14:14:42
# User@Host: concern[concern] @ [192.168.1.90]
# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 134694
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/1817' OR uid > 0) AND sid = 'unkjgt8v74ncgkn1ju9m9ad8m1' ORDER BY timestamp DESC LIMIT 0, 1;
# Time: 090607 14:15:04
# User@Host: concern[concern] @ [192.168.1.90]
# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 134706
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/3740' OR uid > 0) AND sid = 'u7t4t3dm190jpmkdp221j7lia6' ORDER BY timestamp DESC LIMIT 0, 1;It seems that the statistics module only indexes accesslog_timestamp, uid and aid columns of the accesslog table. Is there any way your module could add an extra indexes to that (probably not such a good idea to mess with a core module like that) or, provide your own database table for faster lookups? Alternatively, is there a setting that I can tweak within statistics_advanced_settings that would reduce the number of these unindexed queries, or make the queries more efficient?