PDA

View Full Version : Arcade query causing database lockups


Spinball
27 Oct 2008, 11:16
SELECT user.userid, user.avatarid, user.avatarrevision, avatar.avatarpath, customavatar.filename, NOT ISNULL(filedata) AS hascustom, customavatar.dateline FROM user AS user LEFT
JOIN avatar AS avatar ON avatar.avatarid = user.avatarid LEFT JOIN customavatar AS customavatar ON customavatar.userid = user.userid WHERE customavatar.userid=238420 OR user.userid=238420;

is grinding our database to a halt.
Is there any way to stop all avatar activity, or have I got to uninstall the arcade?

MrZeropage
27 Oct 2008, 22:15
if you have at least mySQL 4.1:

open /arcade/functions/dbclass.php

search for$the_query = "SELECT user.userid, user.avatarid, user.avatarrevision, avatar.avatarpath, customavatar.filename, NOT ISNULL(".$avatarkompatibel.") AS hascustom, customavatar.dateline FROM ibf_user AS user LEFT JOIN ibf_avatar AS avatar ON avatar.avatarid = user.avatarid LEFT JOIN ibf_customavatar AS customavatar ON customavatar.userid = user.userid WHERE customavatar.userid=" . $idneeded . " OR user.userid=".$idneeded;

and replace it with

$the_query = "SELECT user.userid, user.avatarid, user.avatarrevision, avatar.avatarpath, customavatar.filename, NOT ISNULL(".$avatarkompatibel.") AS hascustom, customavatar.dateline FROM (SELECT * FROM ibf_user WHERE userid=".$idneeded.") AS user LEFT JOIN ibf_avatar AS avatar ON avatar.avatarid = user.avatarid LEFT JOIN ibf_customavatar AS customavatar ON customavatar.userid = user.userid WHERE customavatar.userid=" . $idneeded . " OR user.userid=".$idneeded;

This should help (and is included in next v2.6.8+)