Last modified: 2010-05-15 15:33:05 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.
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'
the latest change breaks with mysql 3.23
Created attachment 260 [details] Backport of patch to HEAD to use union for blocklog selects
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.
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).
+ } 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
Marked as fixed-in-cvs. Since we're starting the 1.5 beta cycle, resolving as FIXED.
