[4027] Slow MySQL Query in ManageBans.php

[vmlinuz82]

I think i find a bug in query in updateBanMembers function, we have a more than 140 000 members in smf_members table and executing a new ban function is very slow, this query take near 60 second to execute:

Code Select Expand


SELECT mem.ID_MEMBER, mem.is_activated + 10 AS new_value
FROM ({$db_prefix}ban_groups AS bg, {$db_prefix}ban_items AS bi, {$db_prefix}members AS mem)
WHERE bg.ID_BAN_GROUP = bi.ID_BAN_GROUP
AND bg.cannot_access = 1
AND (bg.expire_time IS NULL OR bg.expire_time > " . time() . ")
AND (mem.ID_MEMBER = bi.ID_MEMBER OR mem.emailAddress LIKE bi.email_address)
AND mem.is_activated < 10


I rewrote query with much faster syntax

Code Select Expand


SELECT mem.ID_MEMBER, mem.is_activated +10 AS new_value
                            FROM {$db_prefix}ban_groups AS bg
                            LEFT JOIN  {$db_prefix}ban_items AS bi ON bg.ID_BAN_GROUP = bi.ID_BAN_GROUP
                            LEFT JOIN {$db_prefix}members AS mem ON mem.ID_MEMBER = bi.ID_MEMBER
                            WHERE bg.cannot_access = 1
                            AND mem.is_activated < 10
                            AND (
                                bg.expire_time IS NULL
                                OR bg.expire_time >" . time() . "
                            )
                            AND (
                                mem.ID_MEMBER = bi.ID_MEMBER
                                OR mem.emailAddress LIKE bi.email_address
                            )


Can you look at my code and tell me if i done something wrong.
Thanks for all help!

[Oya]

the only reason i can imagine this would be faster is because the is_activated branch is being processed before the more expensive branches by the query optimizer

you do have left joins rather than inner joins which may have an impact, but that said, using left joins rather than a forced inner join could have unexpected consequences

[vmlinuz82]

Ok, but when i change LEFT with INNER JOIN the query took about the same time for execution,
and i think there is maybe a problem with my logic, because i receive no result from two queries i cannot compare results.

[Oya]

that suggests then that the critical factor is the ordering of the clauses for the query optimizer

out of interest what version of mysql?

i also wonder if this hasn't been changed in 2.0 anyway

[Nibogo]

I think we're talking about the same function: http://dev.simplemachines.org/mantis/view.php?id=4027 (Queries are different (but similar), however that could be due to changes in 2.0).

[ziycon]

Has anyone tested the suggestion on the bugtracker? I don't have enough bans to test.