Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+1 vote
1.2k views
in Q2A Core by
I was trying to count the userpoints per category. The query works if I use a single level of category match. But if I try to get the points on subcategories too, the query gets too slow as it runs on uservotes table having thousands of rows. Is there a better way to do this? The current query is as follows with part of it coming from points update function in the core.

 $catfilter = "  and (userid_src.categoryid = b.categoryid or userid_src.categoryid  in  (
select categoryid from ^categories where parentid = b.categoryid)
or userid_src.categoryid  in  (
select categoryid from ^categories where parentid in (select categoryid from ^categories where parentid = b.categoryid)))";

$query1 = "insert into ^catpoints (categoryid, userid, points)  (select  b.categoryid,#, ".
$calculations['aselecteds']['multiple']."*(select  ".$calculations['aselecteds']['formula'].$catfilter.")+".
$calculations['avoteds']['multiple']."*(select  ".$calculations['avoteds']['formula'].$catfilter.")
as points
 from  ^categories b) on duplicate key update ^catpoints.points=points";

1 Answer

+1 vote
by
I did a substitution and used

$catfilter = "  and (userid_src.catidpath1 =b.categoryid or userid_src.catidpath2 = b.categoryid or userid_src.categoryid = b.categoryid)"

This improved the query time from about 4s to 1s. Still quite slow to be used on a production site as it directly affects the user interaction -- they have to wait longer to get result from voting or posting. I suppose the plugin is useless unless there is a way to pass categoryid to the concerned function above.
by
edited by
Hello, this change working good, but it's updating  qa_catpoints ONLY points column, NO updates for netvotes, aselecteds. Maybe there is some bug in query? Can You check this, please? :)

Maybe this will help You: when I commented query 1 to run:
    //    qa_db_query_raw(str_replace('~', "='".qa_db_escape_string($userid)."'", qa_db_apply_sub($query1, array($userid))));

and uncomment query2 with same code, it's working correctly. So this code need some fix, to run all queries in one run, not only first :)
by
Hi, yes, it needs to be fixed. But is the query running without causing a performance issue for you?
by
earlier version is working slow , about 4s . new version is working correct (about 1s) but only updating points column, without the rest - other queries don't run now , only first.
by
okay. But even 1s is not good for the customer experience. I'll try some filters and update the code. The original Q2A code takes some milliseconds only.
by
ok, i'm looking forward for update :) how much time You need to fix this?
by
Please do a pull, the query speed is not improved though. It is around 2s now.
by
...I did it, but it's still NOT updating other columns like netvotes or aselected. only points :(
by
@pupi1985 - maybe You can fix this? ;)
by
That issue is fixed now. You can do a git pull for a new version with minor changes.
...