mediawiki block table indexing - Wikitech-l

26 Sep 2005


      I just had a look at the current version of CVS HEAD mediawiki and it
would appear to me that bug 1244[1] (block table access doing a full
scan of the block table) is back or has never left.
I've forgotten most of the intricacies though, so perhaps I'm
mistaken. I seem to recall that even if the query was being run with
FOR UPDATE, a union (instead of OR) needed to be used to prevent the
table scan (which was only supported in mysql4).
Was the solution to this problem moved off elsewhere, or is this
performance problem back?
1: http://bugzilla.wikipedia.org/show_bug.cgi?id=1244
Here is what I thought was the relevant section of Block.php:load()
for your viewing ease:
function load( $address = '', $user = 0, $killExpired = true )
{
   global $wgAntiLockFlags;
   $fname = 'Block::load';
   wfDebug( "Block::load: '$address', '$user', $killExpired\n" );
$ret = false;
   $killed = false;
   if ( $this->forUpdate() ) {
      $db =& wfGetDB( DB_MASTER );
      if ( $wgAntiLockFlags & ALF_NO_BLOCK_LOCK ) {
         $options = '';
      } else {
         $options = 'FOR UPDATE';
      }
   } else {
      $db =& wfGetDB( DB_SLAVE );
      $options = '';
   }
   $ipblocks = $db->tableName( 'ipblocks' );
if ( 0 == $user && $address=='' ) {
      $sql = "SELECT * from $ipblocks $options";
   } elseif ($address=="") {
      $sql = "SELECT * FROM $ipblocks WHERE ipb_user={$user} $options";
   } elseif ($user=="") {
      $sql = "SELECT * FROM $ipblocks WHERE ipb_address='" . $db->strencode( $address ) . "' $options";
   } elseif ( $options=='' ) {
# If there are no options (e.g. FOR UPDATE), use a UNION
#  so that the query can make efficient use of indices
$sql = "SELECT * FROM $ipblocks WHERE ipb_address='" . $db->strencode( $address ) .
         "' UNION SELECT * FROM $ipblocks WHERE ipb_user={$user}";
   } else {
# If there are options, a UNION can not be used, use one
# SELECT instead. Will do a full table scan.
      $sql = "SELECT * FROM $ipblocks WHERE (ipb_address='" . $db->strencode( $address ) . "' OR ipb_user={$user}) $options";
   }
...
-- 
Frank v Waveren                                  Key fingerprint: BDD7 D61E
fvw@var.cx                                              5D39 CF05 4BFC F57A
Public key: hkp://wwwkeys.pgp.net/468D62C8              FA00 7D51 468D 62C8