Slow Query - Ipb 3.4.6

[Fmoraes]

Hello,

I face some problems of slow queries, after a log analysis found that the query below is affecting the performance of my community.

SELECT COUNT( * ) as cnt FROM ipb_members m WHERE m.members_l_display_name !='' AND m.members_l_display_name  IS NOT NULL  AND m.member_group_id NOT IN(5) AND m.member_banned=0 AND ( ! ( members_bitoptions & 1 ) != 0) ;

+----------+
| cnt      |
+----------+
| 507541   |
+----------+
1 row in set (3.31 sec)

It would be possible to create index, safely, to improve the performance of query??

Making it clear that I have no hooks installed, only the ipb 3.4.6.

[Luis Manson]

i would run that with an EXPLAIN

[Grumpy]

First, congrats on getting half a million members.

Do you have caching installed? I haven't checked the code, but I would think something like this should get cached. It wouldn't be too difficult to write a small addon to cache this portion as well if it's not. Alternatively, you can alter the query itself. Get rid of all the WHERE conditions. It'll make it overestimate, but I frankly think that doesn't matter much. And then the query itself would be something the DB already knows the answer to; yielding an instant answer.

The query itself is certainly not an efficient one. Given how it's structured, it'd probably use a key like member_banned which isn't all that practical for the purpose of that query. Because of the last item in the query with bit option, it won't really be possible to have a perfect index either.

[rct2·com]

To be pedantic for a moment, not that it will significantly change execution time, I cannot get my head round

! ( members_bitoptions & 1 ) != 0

Isn't that the same as

( members_bitoptions & 1 ) = 0

Back to the question. Did you try SQL_CALC_FOUND_ROWS instead of COUNT(*) ? It *can* perform better on unindexed tables.