StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL query hanging with LEFT JOIN
text
Body
copied!<p>I'm only a dabbler in databases - using them for backends for basic web applications, never anything enterprise.</p> <p>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.</p> <p>The query is as follows:</p> <pre><code>SELECT * FROM call_status LEFT JOIN queue_log ON call_status.callId = queue_log.callid ORDER BY call_status.callId DESC limit 10; </code></pre> <p>When checking the <em>processlist</em>, I see the following:</p> <pre><code>Query 643 Copying to tmp table SELECT * FROM call_status LEFT JOIN queue_log ON call_status.callId = queue_log.callid ORDER BY call </code></pre> <p>Yes, the query has been running for over 10 minutes.</p> <p>I've run an <em>explain</em> and this it the output:</p> <pre><code>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 </code></pre> <p>I've run a <em>SHOW INDEX</em> on both tables, both look fine to me:</p> <p><strong>call_status:</strong></p> <pre><code>call_status 0 PRIMARY 1 callId A 28954 (null) (null) BTREE call_status 0 ix_order_callId 1 callId A 28954 (null) (null) BTREE </code></pre> <p><strong>queue_log:</strong></p> <pre><code>queue_log 1 ix_queue_log_callid 1 callid A 26083 (null) (null) BTREE </code></pre> <p>I'm at a complete loss, and it's 2am here. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload