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
The query is limited to 25 rows, I can't see what is slowing it down
MySQL ServerSQL
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.
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.