MySQL nested join ordering

Question

I am trying to get a list of "active" users ordered by their last activity and paginated. I had it working originally as a basic join, but when I added a few 100,000's of test users, the query would take longer and longer as you paginated and take absolutely ages by the time you got to the last page.

I then nested the join to fix the speed issues, but I am unable to order the results properly due to the outer query which returns only active users with the offset and limit before the order can be applied.

Here's the current query;

$sql = "SELECT * FROM (
    SELECT roles_users.*, users.last_active FROM roles_users, users
    WHERE roles_users.role_id = '2'
    LIMIT $offset, $limit
    ) AS role
    LEFT JOIN users ON users.id = role.user_id
    WHERE
    users.f_name IS NOT NULL
    AND users.city IS NOT NULL
    AND users.gender IS NOT NULL
    AND users.date_of_birth IS NOT NULL
    ORDER BY users.last_active DESC";

What is the best way of doing this so that it runs efficiently and orders correctly?

EDIT

Table structure for roles_users;

#-- Field -- Type ----------- Null -- Key --- Foreign key --#
#
# user_id   int(10) unsigned  No    primary  users.id
# role_id   int(10) unsigned  No    primary  user_roles.id
#
#############################################################

The users table is quite extensive so I won't put it here. It is fairly standard though.

There is an index on users.last_active.

Note that `LEFT JOIN y ... WHERE y ... [IS NOT NULL]` is the same as `INNER JOIN y ON...` — Strawberry, Apr 06 '16 at 23:05

score 0 · Answer 1 · edited Apr 13 '17 at 12:42

0

(I cannot clearly understand your query. Can you put the table definition ?)

Here are some pointers :

Your ORDER BY/LIMIT should be on the top select not in a sub select.

If you want to achieve a stable query time over pagination with order by/limit you MUST have your order by use an index.

I think your request can be easily rewrite to NOT use a sub query AND/OR you can create the appropriate index.

If you cannot rewrite your select, use a table you will populate every N minutes with the result of you query with the appropriate index and use this table to retrieve data. Maybe a materialized view can do the trick.

https://dba.stackexchange.com/questions/86790/best-way-to-create-a-materialized-view-in-mysql

EDIT :

SELECT roles_users.*, users.last_active FROM roles_users, users WHERE users.id = role.user_id AND roles_users.role_id = '2' AND users.f_name IS NOT NULL AND users.city IS NOT NULL AND users.gender IS NOT NULL AND users.date_of_birth IS NOT NULL ORDER BY users.last_active DESC LIMIT 0, 100

Create an index on users.last_active

edited Apr 13 '17 at 12:42

Community

1
1

answered Apr 06 '16 at 20:27

quazardous

846
10
15

I edited my question. There is already an index on users.last_active. Your query is pretty much what I started off with and takes 46 seconds to run the query to display the last page. What I am trying to do should be simple, but doesn't seem to be for some reason. – Ally Apr 06 '16 at 21:53
Try without the join with role. What s the time ? – quazardous Apr 06 '16 at 21:57
You could store the last minimum of last_active value to reduce the range – quazardous Apr 06 '16 at 21:58
What is the cardinality of the last_active index ? And the number of row ? – quazardous Apr 06 '16 at 22:05
Sorry to ask because I want to be sure: is there an index on the role_id field alone or at least with the field at first index column. – quazardous Apr 06 '16 at 22:12
I tried with out the join... just pulling users, and that's twice as fast but still 16 seconds. Not sure what you mean by "store the last minimum of last_active to reduce range". Cardinality of last_active idx is 299512 NULL yes. Number of rows 297385. – Ally Apr 06 '16 at 22:14
And what is the explain ? Range scan ? For the minimum : it was BS. I sought u could use the last value of last_active to narrow the results of the next page but you'll lose the limit values... – quazardous Apr 06 '16 at 22:19
role_id is a foreign key. – Ally Apr 06 '16 at 22:33
Check this post http://stackoverflow.com/q/304317/2365940 you may want to manually create index for foreign key – quazardous Apr 09 '16 at 11:15
A foreign key is an index! What do you mean? – Ally Apr 17 '16 at 20:22
Seams logic for you and me but not so evident for the optimizer... worth the try. – quazardous Apr 18 '16 at 04:18

MySQL nested join ordering

1 Answers1