Last modified: 2010-05-15 15:33:38 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 1493 - Logging table needs log_timestamp index
Logging table needs log_timestamp index
Status: RESOLVED FIXED
Product: MediaWiki
Classification: Unclassified
Special pages (Other open bugs)
1.4.x
All All
: Normal normal with 1 vote (vote)
: ---
Assigned To: Nobody - You can work on this!
:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2005-02-08 10:11 UTC by Jamesday
Modified: 2010-05-15 15:33 UTC (History)
0 users

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments

Description Jamesday 2005-02-08 10:11:20 UTC
From the slow query log of holbach:

# Query_time: 1  Lock_time: 0  Rows_sent: 50  Rows_examined: 
28229
SELECT log_type, log_action, log_timestamp,
                        log_user, user_name,
                        log_namespace, log_title, cur_id,
                        log_comment, log_params FROM `user`, 
`logging` LEFT OUTER JOIN `cur` ON 
log_namespace=cur_namespace AND log_title=cur_title WHERE 
user_id=log_user ORDER BY log_timestamp DESC  LIMIT 0,50;

The explain:

explain SELECT log_type, log_action, log_timestamp,
    ->                         log_user, user_name,
    ->                         log_namespace, log_title, 
cur_id,
    ->                         log_comment, log_params FROM 
`user`, `logging` LEFT OUTER JOIN `cur` ON 
log_namespace=cur_namespace AND log_title=cur_title WHERE 
user_id=log_user ORDER BY log_timestamp DESC  LIMIT 0,50\G
*************************** 1. row 
***************************
        table: logging
         type: ALL
possible_keys: user_time
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 27972
        Extra: Using filesort
*************************** 2. row 
***************************
        table: user
         type: eq_ref
possible_keys: user_id
          key: user_id
      key_len: 4
          ref: logging.log_user
         rows: 1
        Extra:
*************************** 3. row 
***************************
        table: cur
         type: ref
possible_keys: cur_title,name_title_timestamp
          key: cur_title
      key_len: 255
          ref: logging.log_title
         rows: 1
        Extra:

Note the type: ALL for the logging table and the Extra: 
Using filesort as well as the estimated 27972 rows. It's 
doing a full table scan because there's no index which lets 
it get the records in timestamp order.

I suggest adding an index log_timestamp (log_timestamp) .

Assuming that makes sense, let me know and I'll put it live 
on the Wikimedia servers next low load time.

It's also using the wrong index for cur but that may clear 
up after this change.
Comment 1 Jamesday 2005-11-12 23:30:50 UTC
The index times (log_timestamp) is present on enwiki but 
not plwiki or frwiki. Is this logging query still used? If 
it is, we'll need that index.
Comment 2 Rob Church 2007-01-02 23:47:57 UTC
We've got a pure index on log_timestamp, and it's also included in all the other
indexes on that table, now. Closing as FIXED.

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links