SQL query help

Question

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";

Answer 1

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.

Comments

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 :)

---

Hi, yes, it needs to be fixed. But is the query running without causing a performance issue for you?

---

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.

---

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.

---

ok, i'm looking forward for update :) how much time You need to fix this?

---

Please do a pull, the query speed is not improved though. It is around 2s now.

---

...I did it, but it's still NOT updating other columns like netvotes or aselected. only points :(

---

@pupi1985 - maybe You can fix this? ;)

---

That issue is fixed now. You can do a git pull for a new version with minor changes.