Last modified: 2010-05-15 15:33:05 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 1244 - Block::load doesn't use an index.
Block::load doesn't use an index.
Status: RESOLVED FIXED
Product: MediaWiki
Classification: Unclassified
User login and signup (Other open bugs)
1.4.x
All All
: Normal normal with 1 vote (vote)
: ---
Assigned To: Nobody - You can work on this!
: patch
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2004-12-31 10:44 UTC by Jamesday
Modified: 2010-05-15 15:33 UTC (History)
1 user (show)

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


Attachments
Backport of patch to HEAD to use union for blocklog selects (1.31 KB, patch)
2005-02-04 19:25 UTC, Frank v Waveren
Details
Updated patch to not use unions on mysql 3.x (2.07 KB, patch)
2005-02-05 01:34 UTC, Frank v Waveren
Details

Description Jamesday 2004-12-31 10:44:49 UTC
The current block query from Block::load is:

mysql> explain select * from ipblocks where 
(ipb_address='83.24.12.97' or ipb_user=73855);
+----------+------+----------------------+------+---------+--
----+------+-------------+
| table    | type | possible_keys        | key  | key_len | 
ref  | rows | Extra       |
+----------+------+----------------------+------+---------+--
----+------+-------------+
| ipblocks | ALL  | ipb_address,ipb_user | NULL |    NULL | 
NULL |  731 | Using where |
+----------+------+----------------------+------+---------+--
----+------+-------------+

Note that it's doing a full table scan - no index use. 
Change to this:

 explain select * from ipblocks where 
ipb_address='83.24.12.97' union select * from ipblocks where 
ipb_user=73855;
+----------+------+---------------+-------------+---------+--
-----+------+-------------+
| table    | type | possible_keys | key         | key_len | 
ref   | rows | Extra       |
+----------+------+---------------+-------------+---------+--
-----+------+-------------+
| ipblocks | ref  | ipb_address   | ipb_address |      40 | 
const |    1 | Using where |
| ipblocks | ref  | ipb_user      | ipb_user    |       4 | 
const |    1 | Using where |
+----------+------+---------------+-------------+---------+--
-----+------+-------------+

Two parts with a union, so each part can use an index.
Comment 1 JeLuF 2004-12-31 14:20:11 UTC
How to specify FOR UPDATE in a UNION?

SELECT * FROM ipblocks WHERE ipb_address = '127.0.0.1' 
UNION SELECT * FROM ipblocks WHERE ipb_user=33 FOR UPDATE;

=> ERROR 1234: Wrong usage/placement of 'UPDATE'

Comment 2 Christof Damian 2005-01-04 15:43:42 UTC
the latest change breaks with mysql 3.23
Comment 3 Frank v Waveren 2005-02-04 19:25:44 UTC
Created attachment 260 [details]
Backport of patch to HEAD to use union for blocklog selects
Comment 4 Frank v Waveren 2005-02-04 19:27:46 UTC
MySQL 3.23 doesn't do union. If you get the "ERROR 1234: Wrong usage/placement of 'UPDATE'" error when using FOR UPDATE, you'll 
have to upgrade too, it looks like earlier versions of MySQL didn't like the combination of FOR UPDATE and UNION.
Comment 5 Frank v Waveren 2005-02-05 01:34:03 UTC
Created attachment 261 [details]
Updated patch to not use unions on mysql 3.x

Ok, here's a fresh patch that doesn't use UNION on MySQL 3.x. If you're
performance concious and still on MySQL 3.x you'll want to split it into two
separate selects and concat the results in PHP though (though this patch
doesn't make matters worse for you).
Comment 6 JeLuF 2005-03-02 21:46:55 UTC
+                } elseif ( $options=='' || $wgDBmysql4!=true) {

I think this one should be 

+                } elseif ( $options=='' && $wgDBmysql4==true) {

Hardcodes for different databases are quite ugly. We try to move those things
into the Database classes. It would be nice not to have to put this into Block.php
Comment 7 Brion Vibber 2005-05-04 07:03:19 UTC
Marked as fixed-in-cvs. Since we're starting the 1.5 beta cycle, resolving as FIXED.

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


Navigation
Links