Slow queries, need help badly {Urgent!!}{Live627 This is Your Code I need Help!}

[wynnyelle]

I am getting slow queries on my site:

| 40728076 | warrior_acorn | 192.168.1.1:60201 | warrior_smf | Query | 4858 | Sending data | SELECT COUNT(mem.id_member)
FROM smf_members AS mem
LEFT JOIN smf_subaccounts AS sub ON (sub.id |
| 40728689 | warrior_acorn | 192.168.1.1:34790 | warrior_smf | Query | 4833 | Sending data | SELECT COUNT(mem.id_member)
FROM smf_members AS mem
LEFT JOIN smf_subaccounts AS sub ON (sub.id |
| 40728800 | warrior_acorn | 192.168.1.1:35292 | warrior_smf | Query | 4827 | Sending data | SELECT COUNT(mem.id_member)

This seems to be problems with a mod, the subaccount mod that was custom modified by Live.

We have had problems with this mod for a long time. Would anyone be able to give it a little tweak or show me how I could?

[live627]

Which file?

[wynnyelle]

I am not sure, just know that this slowness required a restart of mySQL. I found these in the Cpanel error area earlier.

Subaccounts have often caused slow queries on my site, they are extremely popular hit with the members so they get heavily used.

[live627]

Yep, yep, could be near a million 'subaccounts.

Do the errors include the URL? That, alone, would tell me the file.

[wynnyelle]

Unfortunately they do not.

This has happened again tonight. Site crashed, slow queries, same ones.

Okay--Would it help to first list all the files that are involved with subaccounts?

We can then at least narrow it.

[pixelated]

Hey Live,

I'm currently doing quite a bit of programming for Groovystar, if it helps, I can probably help you get some information. Just let me know what you need and what area it might be in.

~Pixelated

[wynnyelle]

Really need some help here. The problem is happening just about every day now.

[wynnyelle]

bump

[live627]

Okay--Would it help to first list all the files that are involved with subaccounts?

We can then at least narrow it.

• Profile-Trade.php
• Profile-SubGroups.php
• Profile-Parent.php
• Profile-Money.php

Those are the three files that have that particular query snippet.

[live627]

Wow... I wrote some really bad queries. I have an idea to change them from creating some Cartesian coordinate to a known subset. In laymen's terms, they were probably evaluating thousands of members times the subaccount total... possibly millions of rows! The functions for loading members already have the subs' IDs loaded, so I'll use that. Known subset is from algebraic maths - {1,2,4,8} - same  principle applies in SQL. Now, it only evaluates you and your subaccounts - 30-odd for you, 3 for me.

Please try My Money in the profile. That section uses the updated queries. The others haven't been changed yet because I'm waiting on feedback.

[pixelated]

Hey Live,

Thanks for getting back to us!

When trying to access "My Money" the following error is shown.

Whoa! We got a goof here.
Database error, given array of integer values is empty. (subaccounts)

~Pixelated

[live627]

Hmm, it worked for me. Oh, I know what the problem is! You tested it on an account that has no subs.

[pixelated]

I sure did.

Does whatever that function is have to run, or can you only do it if a person has a sub account?

~Pixelated

[wynnyelle]

Thank you so much for getting back to me. We're still having the slow queries almost every day now bringing the site down. So it's moved into "fix it asap" territory.

I suspect that the subaccount mod works fine on smaller forums but is not built to handle a situation where you have literally tens of thousands of members using them.


Adding info here: Quoting posts seems to take a really, really long time to get to the quoting page after you press the quote button. It takes forever.

[wynnyelle]

Bump

[wynnyelle]

BUMP. I really need help with this, it's urgent.

[wynnyelle]

please!!! Live, I need your help, this is your code and so you are the one who would know it best. It is causing our site to crash, please help.

[wynnyelle]

Please help.

[butch2k]

Please capture the full query and run it against the database using EXPLAIN and post the results here.

[butch2k]

Please run the following SQL:
CREATE INDEX idx_id_member  ON smf_subaccounts (id_member)