Optimizing a query qa_userpoints / qa_userscores (yearly scores)

Question

I am saving the userpoints in table userscores on each first of month, this way I have monthly scores. I also do this with yearly scores, however, the query takes ages (2 min?!). I tried to filter out low point users, only need top 20-30, but did not help. Maybe there is a mistake in the query?

SELECT qa_userpoints.userid, qa_userpoints.points - COALESCE(uf.points, 0) AS mpoints
FROM `qa_userpoints`
LEFT JOIN (
SELECT userid, points
FROM `qa_userscores`
WHERE `date` = "2017-01-01"
AND `points` > 100
) AS uf
ON uf.userid = qa_userpoints.userid
AND qa_userpoints.points > 500
ORDER BY mpoints DESC

Anyone has an idea how to optimize?

Answer 1

I'm assuming this is your monthly users plugin? So the userscores table is the same as the one from here.

You don't have an index on the points field in userscores, so in your subquery where you do "points > 100" may be slow (especially if you have many rows in there). Also the table doesn't have a primary key. You'd probably want to make a primary key on "(date, userid)" since that combination should be unique I think.

I'm not sure about your JOIN condition either. You have the same userid in the userscores table multiple times right? One for each month. So that will be taking every row from the userpoints table (many thousands) and combining each one with many rows from the userscores table, making possibly millions of rows in the joined table before filtering.

You are also sorting the query based on a calculated field (mpoints) which cannot be indexed. Coupled with the previous point, if you're trying to sort millions of unindexed rows it will be very slow.

I'm not sure exactly why you are doing that calculation. It seems like you are selecting the user points for January 2017 only, then taking those off the user's total points. I don't see what that is achieving. Don't you want to add up all the user points in a particular year instead? For example this would give you all the years and userids with their total points in those years:

SELECT YEAR(date) AS theyear, userid, SUM(points) AS yearpoints FROM qa_userscores GROUP BY theyear, userid

You could filter by year by doing something like "WHERE date >= '2016-01-01' AND date < '2017-01-01'" for 2016.

Comments

Yes, best user plugin. Scores are saved each month. So 2017-09-01 is 500, then 2017-10-01 is 800. The monthly score is the difference from both: 300. I will study your answer in the next days. Due to private problems I cannot find the peace of mind now :/

---

OK I see. Perhaps it would work better to add an extra column to userscores with the actual points gained since the previous period? Then you can select and sort on that more easily.

---

I set the primary keys by: ALTER TABLE qa_userscores ADD PRIMARY KEY(date, userid) - and now it loads within a second. Thanks a thousand. In case it gets slower with more data in the future, I will consider your other tips! Thanks Scott.