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

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.