Single Query Making CP Unusable

I have exactly the same problem with that query, but no solution yet (sorry)

We have just shy of 300K in our member database and it still causes problems. Our biggest member group - the default Members has a majority of those users and that group has:

Include Members in PUBLISH page multi-author list? NO Include Members in Site’s Member List? NO

But it doesn’t matter as that Query is still too huge.

EE 2.5.2 MSM site

I too am on the lookout for a fix for this.

Have you tried added database indexes to exp_members.in_authorlist and exp_member_groups.include_in_authorlist

Thanks Gareth, I attempted that and don’t think it had any effect.

First of all - I ended up deleting over 200K accounts that were created during a time period where our spam protection was pretty weak. Those 200K hadn’t logged in in over a year and had never made a comment or forum post - so we felt they were safe to remove while we debug this. I made a backup of the db in case.

So then, if I used the EXPLAIN SELECT query on the above questionable query, I get the result:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1 SIMPLE  exp_members  ALL  group_id  NULL NULL NULL 76921 Using filesort
1 SIMPLE  exp_member_groups  eq_ref  PRIMARY  PRIMARY  6 ee2.exp_members.group_id,const  1 Using where

Showing me that on exp_members, group_id is a possible key, but wasn’t used and the query had to be run against 76921 rows.

I ran:

ALTER TABLE exp_members ADD INDEX(in_authorlist);
ALTER TABLE exp_member_groups ADD INDEX(include_in_authorlist);

Then ran the EXPLAIN SELECT again and got:

1 SIMPLE  exp_members  ALL  group_id,in_authorlist  NULL NULL NULL 76921 Using filesort
1 SIMPLE  exp_member_groups  eq_ref  PRIMARY,include_in_authorlist  PRIMARY  6 ee2.exp_members.group_id,const  1 Using where

Nothing changed. The query against exp_members is still being run against every row.

So then I’m thinking, ok, maybe PRIMARY is the problem and I remove that from exp_member_groups - which is weird to have a primary key on it, since there aren’t any unique numbers in it in an MSM environment. Now, I’m not a database expert, but I don’t know how it’s possible to have a Primary Key without something unique to each row.

At any rate, removing the PRIMARY made things worse.

1  SIMPLE  exp_members  ALL  in_authorlist  NULL NULL NULL 76921  Using filesort
1  SIMPLE  exp_member_groups  ref  include_in_authorlist  ee2.exp_members.group_id  18  Using where

Now exp_member_groups is run against 18 rows where before it was just 6.

I couldn’t just add back a Primary Key without a unique number, so I had to create a new column with auto_increment and assign the primary to that column. Still - I can’t get the number of rows used by member_groups back to 6 anymore. Adding an index on the new ID column didn’t help.

So all in all - this is a bit of a failure as far as I can tell. In general, we’re doing ok with under 100K users, but I don’t know how to improve this query for if/when our members table grows to over 300K again.