left join is hanging but join works fine - i need null rows

[optikalefx]

I have a users table, and I have a table of videos that a user has permission for.  So there are many rows in the permissions table for 1 user.

 

I want to show all user info including a count() on the number of videos for all users in 1 list.

 

doing select * from users works fine of course.

but doing

 

select 
users.UserID,
COUNT(userpermissions.`UserPermissionsID`)
FROM users
LEFT JOIN userpermissions ON (userpermissions.UserID = users.UserID)
GROUP BY users.UserID
LIMIT 200

 

hangs and never finishes.  Even with a limit of 1.  There are 16,000 users and 27,000 total permissions.

 

What does work - kind of. Is doing JOIN instead of LEFT JOIN.  But that obviously won't grab users that have no videos.

So what can i do to make the LEFT JOIN query work? Do i have an index problem? Or are these tables too large for the left join?

[The Little Guy]

do you have any indexes?

 

run this:

 

explain select 
users.UserID,
COUNT(userpermissions.`UserPermissionsID`)
FROM users
LEFT JOIN userpermissions ON (userpermissions.UserID = users.UserID)
GROUP BY users.UserID
LIMIT 200

 

Left joins are fast, as long as it is indexed properly.

 

I run left joins on tables that have 200 million rows, the tables are indexed nicely, and the queries usually take less than 1 second to run.

[optikalefx]

Thats odd.

 

users is

type: index

key: Primary

key_len:4

rows: 16016

extraL Using index: using temporary; using filesort

 

userpermissions however has

type: all

key:null

rows: 27000

 

But i have a primary key auto increment set on the table... I wonder why none is showing up here... hmm

[optikalefx]

Well now that i understand what its telling me - this result makes sense.  It is a 1 to many relationship.

 

Its not the left join alone that is causing the problem. Its left joining with the aggregate function like count or group by that is causing the issue.

[The Little Guy]

Index: UserPermissionsID (if you haven't already)

[btherl]

TLG, do you mean index on userpermissions.UserID ?

[The Little Guy]

yeah, that would probably be better

 

but you could try UserPermissionsID and see if it likes that better or not.

[optikalefx]

Ok, can you explain why that worked? I put an index on userpermissions.UserID and now it works.

 

So... why?

[The Little Guy]

an index is a way for mysql to quickly search a database, it is like a reference to where the row is in the database.

 

Basically adding that index, mysql no longer has to search every row in the database, it only has to search rows that it knows are relevant.

[btherl]

Typically a left join will fetch matching rows from the left table (users) and then look up matches in the right table (userpermissions).  The index you created allows it to look up those matches in userpermissions very fast.  Previously it had to examine all 27,000 rows in userpermissions to see which ones match.

 

A good analogy is a book index - it'll take a long time to read 300 pages looking for a mention of "index optimization", but it's very fast to look at the index at the back of the book and get a list of which pages mention it.

[The Little Guy]

A good analogy is a book index - it'll take a long time to read 300 pages looking for a mention of "index optimization", but it's very fast to look at the index at the back of the book and get a list of which pages mention it.

 

I like that analogy!