asked on
MySQL join same table on two fields
I have a table with two user id fields
I need to get the username in one query for each of the two ID's
When just grabbing one username the query is fine, trying to add the second username then I get timeouts
Here's a start of the query
The query is limited to 25 rows, I can't see what is slowing it down
I need to get the username in one query for each of the two ID's
When just grabbing one username the query is fine, trying to add the second username then I get timeouts
Here's a start of the query
SELECT * from threads left join users on users.id=thread_user
left join users as user_last on users.id=thread_user_last
where ref_id=26 order by thread_datetime desc limit 25
The query is limited to 25 rows, I can't see what is slowing it down
MySQL ServerSQL
Last Comment
Nicholas
How many rows in the table? The limit of 25 occurs AFTER the whole table has been searched and sorted.
ASKER
Two millions rows
Threads table
PRIMARY threads_table_id
INDEX thread_user
INDEX thread_user_last
Users table
PRIMARY id
Threads table
PRIMARY threads_table_id
INDEX thread_user
INDEX thread_user_last
Users table
PRIMARY id
Well, that's a lot if I/O happening. Also, large numbers of columns and/or large column sizes can acerbate the problem.
Are the columns you are joining on indexed? If not, that will also cause a lot of unnecessary I/O and comparison operations.
You didn't say how long the query runs before timeout. Increasing that timeout value may help, but ensuring the correct columns are indexed and limiting the columns being select to just the ones necessary should be the first steps.
Are the columns you are joining on indexed? If not, that will also cause a lot of unnecessary I/O and comparison operations.
You didn't say how long the query runs before timeout. Increasing that timeout value may help, but ensuring the correct columns are indexed and limiting the columns being select to just the ones necessary should be the first steps.
ASKER
Table indices are above
With a single username join the query is immediate (0.5 secs), with the second one it takes over 88 seconds
With a single username join the query is immediate (0.5 secs), with the second one it takes over 88 seconds
ASKER
Maybe the Explain will help because I'm confused
id? select_type? table? partitions? type? possible_keys? key? key_len? ref? rows? Extra?
1 SIMPLE threads NULL ALL thread_user,thread_user_last NULL NULL NULL 1829273 Using where; Using temporary; Using filesort
1 SIMPLE users NULL eq_ref PRIMARY PRIMARY 4 .threads.thread_user 1
1 SIMPLE user_last NULL ALL NULL NULL NULL NULL 877517 Using join buffer (flat, BNL join)
have you put the query into the Workbench and executed the "visual explain current query" to see if it shows where the excessive time might be coming from? Might be useful to do the same thing with the query that runs quickly and compare them.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Glad you found it! I forgot to ask which version of MySql you are running? Or is it MariaDB?
ASKER
10.1.21-MariaDB
ASKER
Still doesn't make sense why a buffered join screws it up...?
"changing the query to a nested select seperately within the same query"
Could you post the final fixed query for other readers to benefit from?
Could you post the final fixed query for other readers to benefit from?
ASKER
SELECT *,(select username from users where users.id=thread_user_last)
ASKER
Worked it out myself