Slow Queries - Optimisation

[heusdens]

Hi

I'm started to log all slow queries and my logs are filling up with hundreds of these following query/s:

Code Select Expand

SELECT bi.id_ban, bi.email_address, bi.id_member, bg.cannot_access, bg.cannot_register,
bg.cannot_post, bg.cannot_login, bg.reason, IFNULL(bg.expire_time, 0) AS expire_time
FROM smf_ban_items AS bi
INNER JOIN smf_ban_groups AS bg ON (bg.id_ban_group = bi.id_ban_group AND (bg.expire_time IS NULL OR bg.expire_time > 1389720282))
WHERE
(((66 BETWEEN bi.ip_low1 AND bi.ip_high1)
AND (249 BETWEEN bi.ip_low2 AND bi.ip_high2)
AND (78 BETWEEN bi.ip_low3 AND bi.ip_high3)
AND (234 BETWEEN bi.ip_low4 AND bi.ip_high4)) OR ((66 BETWEEN bi.ip_low1 AND bi.ip_high1)
AND (249 BETWEEN bi.ip_low2 AND bi.ip_high2)
AND (78 BETWEEN bi.ip_low3 AND bi.ip_high3)
AND (234 BETWEEN bi.ip_low4 AND bi.ip_high4)));

Any idea on how this can be optimised / resolved?  I understand what this query is doing but stuck at how to solve it.  Only 9 rows are being examined here. 

[青山 素子]

How many bans do you have set up in SMF?

[heusdens]

Hi

There is only one ban in place.

[butch2k]

could you post an explain of this query (using phpmyadmin for instance) ?

[Arantor]

Waste of time, basically. The entire ban system is, end to end, a series of slow queries and needs a complete rewrite - and it's worse in 2.1.

[butch2k]

While i agree on the performance of the ban system is far from being stellar, having hundreds of such queries piling up is not normal given there are just 9 rows being examined. I suspect there might be some issue elsewhere i just need to make sure there is no index issue at hands.

[heusdens]

Hi

Apologies I didn't see the responses sooner.

Here is output of EXPLAIN:

Code Select Expand

Generation Time: Jan 24, 2014 at 04:41 PM
Generated by: phpMyAdmin 4.0.8 / MySQL 5.5.34-cll
SQL query: EXPLAIN SELECT bi.id_ban, bi.email_address, bi.id_member, bg.cannot_access, bg.cannot_register, bg.cannot_post, bg.cannot_login, bg.reason, IFNULL(bg.expire_time, 0) AS expire_time FROM smf_ban_items AS bi INNER JOIN smf_ban_groups AS bg ON (bg.id_ban_group = bi.id_ban_group AND (bg.expire_time IS NULL OR bg.expire_time > 1389720282)) WHERE (((66 BETWEEN bi.ip_low1 AND bi.ip_high1) AND (249 BETWEEN bi.ip_low2 AND bi.ip_high2) AND (78 BETWEEN bi.ip_low3 AND bi.ip_high3) AND (234 BETWEEN bi.ip_low4 AND bi.ip_high4)) OR ((66 BETWEEN bi.ip_low1 AND bi.ip_high1) AND (249 BETWEEN bi.ip_low2 AND bi.ip_high2) AND (78 BETWEEN bi.ip_low3 AND bi.ip_high3) AND (234 BETWEEN bi.ip_low4 AND bi.ip_high4)));
Rows: 2

id   select_type   table   type   possible_keys   key   key_len   ref   rows   Extra   
1   SIMPLE   bg   ALL   PRIMARY   NULL   NULL   NULL   4   Using where
1   SIMPLE   bi   ref   ID_BAN_GROUP   ID_BAN_GROUP   2   phlepw_smf.bg.id_ban_group   1   Using where

[butch2k]

Ok you probably have an issue elsewhere (locked table) resulting in queries pilling up until the blocking one is done for, or you are being seriously hammered by requests. Do you have an idea of the number of requests hitting SMF/index.php per sec ?