Avatar of Nicholas
Nicholas
 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

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

Open in new window


The query is limited to 25 rows, I can't see what is slowing it down
MySQL ServerSQL

Avatar of undefined
Last Comment
Nicholas

8/22/2022 - Mon
JesterToo

How many rows in the table?  The limit of 25 occurs AFTER the whole table has been searched and sorted.
Nicholas

ASKER
Two millions rows


Threads table
PRIMARY      threads_table_id
INDEX      thread_user
INDEX      thread_user_last


Users table
PRIMARY      id
JesterToo

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Nicholas

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
Nicholas

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)

Open in new window

JesterToo

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Nicholas

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Nicholas

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
JesterToo

Glad you found it!  I forgot to ask which version of MySql you are running?  Or is it MariaDB?
Nicholas

ASKER
10.1.21-MariaDB
Nicholas

ASKER
Still doesn't make sense why a buffered join screws it up...?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PortletPaul

"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?
Nicholas

ASKER
SELECT *,(select username from users where users.id=thread_user_last) as user_last from threads join users on users.id=thread_user where...
Nicholas

ASKER
Worked it out myself
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.