(Solved?) Database slow response

Yes... I had to edit all queries using IN SET, and now the site goes smootly.

I think it must be a warning about this in the page for download of efICTION, to avoid other users to have the same problem.

Sincerely

Ayesha

The fundamental problem here isn't the FIND_IN_SET.  It's that we're calculating the number of stories the person has authored on the fly.  Even if you moved the coauthors to a separate table, with a large number of stories and authors it's still going to be slow calculating those numbers.  The solution is going to be adding a new integer field to the _authorinfo table to keep a count of the stories they member has authored. 

That would resolve the issues specifiic to the count query. However, it would not help with search queries where you are displaying a list of stories the author has written/co-authored, which still impact the database due to find in set.

Will disabling

define ("_MEMBERCOUNT", "SELECT COUNT(DISTINCT "._UIDFIELD.") FROM "._AUTHORTABLE." LEFT JOIN ".TABLEPREFIX."fanfiction_stories as stories ON stories.validated > 0 AND (FIND_IN_SET("._UIDFIELD.", stories.coauthors) > 0 OR stories.uid = "._UIDFIELD.")  LEFT JOIN ".TABLEPREFIX."fanfiction_authorprefs as ap ON "._UIDFIELD." = ap.uid");

help with this co-author problem or will it just make it worse? I have no idea how to add an new integer field to the _authorinfo table to keep a count of the stories they member has authored.

It will break your members page and several others.

Well, can you tell me how to add this fix that you listed above? Cause if I don't fix this problem, they are going to disable my database. Will just turning off co-authors help? Or will it make any difference?

Contact me off list if you're willing to be my guinea pig on a fix.

Hi!

I run a small private hosting solution, and one of my "customers" uses efiction system. They constantly hang my database server with only a few SQL queries (specifically those co-author selects and counts). These queries runs for about 40 seconds each consuming a lot of memory and IO.

This is the same problem Ayesha reported.

Ayesha: can you send me a patch with the changes you did in the code ?

Tammy: Is there any solution for this in a development version, or a separate pathc ?

We're almost ready to release version 3.4 which will be this fix.  I need to update the update scripts a bit before releasing it. It should be ready this weekend.  That's what I thought last weekend, but RL issues came up.

Tammy

Yeeeah! Thanks Tammy!

I have the same problem with "_MEMBERCOUNT", so I will wait the update anxiously 😀

( please excuse my awful english 😳 )