Indexes in the accesslog table

Below is the SQL for adding indexes for the mentioned fields.
alter table accesslog add key(uid);
alter table accesslog add key(path);
alter table accesslog add key(url);

This is best brought up on the Performance and scalability forum for discussion rather than submitting it as a bug.

The system works (albeit in your numble opinion slower than could be) but works none the less. So at this stage it isn't a bug, it's a feature request.

Marking as such.

regards,
--AjK

I'm in agreement with the original poster that this is a bug. The "track page visits" (user/####/track/navigation) page provided by the statistics.module requires a full table scan to complete. On a site that is busy or one that keeps a long history of access logs - or both - this could require scanning a million or more rows to return 10 or 20 rows. Adding an index on uid makes a dramatic performance difference in this query.

The issue has sat here for over a year. Did discussing it in the performance and scalability forum take the issue anywhere? I'm not able to find the thread if it did.

Scott

Related issue: http://drupal.org/node/164532 - indexes for another tables.

I think this should go into Drupal 6.x

I agree. Thanks for cross-linking the two issue threads.

Specifically see this patch http://drupal.org/node/164532#comment-620063

Which adds:

-    'indexes' => array('accesslog_timestamp' => array('timestamp')),
+    'indexes' => array(
+      'accesslog_timestamp' => array('timestamp'),
+      'uid' => array('uid'),
+    ),

Marking this as duplicate, since that issue is critical, and has a patch ;)