Why does this LEFT JOIN crash the server

#1

Hey all,

We have a SELECT with LEFT JOIN that is nothing big deal, and in fact the MySQL server indicates that the command was executed in 0.0654 Sec, yet NO results are returned and instead this MySQL command is running on the process list until the Server crashes. What is the problem this SELECT:

SELECT pnu.user_id, pnu.nick_name, pnu.email, pnu.category, pau.id, pau.user_status AS client_stat
FROM pnu
LEFT JOIN pau
USING(email)
WHERE pnu.user_id < 2516479
AND pnu.user_status = 1
ORDER BY pnu.user_id DESC LIMIT 100;

#2

that looks okay…

how many rows in each table?

#3
  1. Are both email fields indexed?

  2. What says explain?

#4

Is this difference

correct?

#5

There are about 2-Million records in pnu Table
and about 20,000 rows in pau Table.

So the number of records should not be an issue, since we have Tables with 7-Million+ records and I have LEFT JOINS on them coming back with millisecond results.

#6

droop,
I am sorry but I do not understand what you are asking!

#7

Does the USING change the query to a de facto INNER JOIN ?

#8

There’s a difference between those two column names, one is from pnu. and the other is from pau., I wondered if that was intentional. I don’t imagine it would cause a server crash if it was a typo, but it was just a thought.

#9

no :slight_smile:

1 Like
#10

No, there are 2 different Tables: the left of LEFT JOIN is pnu and the right side is pau

#11

Yes, USING is just equivalent to ON (x.email = y.email) or USING(email)

Really puzzling problem/failure !!!
I mean it should work just fine, but it is not!

You know I am thinking maybe the problem is that MySQL is going through entire 2-Million+ records on pnu Table! But then this should not be the case since we have:
ORDER BY pnu.user_id DESC LIMIT 100;

Hum !

#12

How have you set it up so that the query does the ORDER BY and LIMIT without looking at all the rows to determine what the user_id values are?

1 Like
#13

show us the EXPLAIN, please