CODEWITHSUNDEEP
mysql
Whitey
Whitey

Reputation: 3

MySQL query hanging with LEFT JOIN

I'm only a dabbler in databases - using them for backends for basic web applications, never anything enterprise.

I've recently needed to work with two tables - one with 130,000 rows and the other with 29,000 rows. They have similar data and I need to pull from them with some sort of JOIN statement. Unfortunately this seems to cause issues and the query sits and hangs.

The query is as follows:

SELECT * 
FROM call_status LEFT JOIN queue_log 
ON call_status.callId = queue_log.callid 
ORDER BY call_status.callId DESC limit 10;

When checking the processlist, I see the following:

Query   643 Copying to tmp table    SELECT * FROM call_status LEFT JOIN queue_log ON call_status.callId = queue_log.callid ORDER BY call

Yes, the query has been running for over 10 minutes.

I've run an explain and this it the output:

1   SIMPLE  call_status ALL (null)  (null)  (null)  (null)  28954   Using temporary; Using filesort
1   SIMPLE  queue_log   ALL ix_queue_log_callid (null)  (null)  (null)  130419

I've run a SHOW INDEX on both tables, both look fine to me:

call_status:

call_status 0   PRIMARY 1   callId  A   28954   (null)  (null)      BTREE   
call_status 0   ix_order_callId 1   callId  A   28954   (null)  (null)      BTREE

queue_log:

queue_log   1   ix_queue_log_callid 1   callid  A   26083   (null)  (null)      BTREE

I'm at a complete loss, and it's 2am here.

Upvotes: 0

Views: 1086

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Can you try this?:

SELECT * 
FROM 
      ( SELECT *
        FROM call_status 
        ORDER BY callId DESC 
        LIMIT 10
     ) AS st
  LEFT JOIN 
      queue_log AS qlog 
    ON 
      st.callId = qlog.callid 
ORDER BY 
      st.callId DESC 
LIMIT 10

Upvotes: 2

bumbu
bumbu

Reputation: 1317

Try something like selecting last 10 call_status

SELECT * FROM call_status ORDER BY callId DESC limit 10;

after that, in server side code (php, asp, jsp, ruby) get a list of all this id's that will look like (3,4,5,6,7,90)

and do

SELECT * FROM queue_log WHERE callid IN (3,4,5,6,7,90)

and join these two lists in you server side code

or you can create virtual tables to do the same thing but on database side

Upvotes: 0

Related Questions