Large query looking for differences in tables causing mysql to crash

[bradical1379]

I have two tables with about 25k rows in each, and I am trying to find the differences between the two tables. The query is causing mysql to crash.

This is the query:

SELECT *
FROM data
WHERE item NOT IN (
SELECT item
FROM data_backup)

The item column is a varchar, but it contains a 13 digit number only. I am trying to find how many items from my inventory appear in one table over the over.

Oh, I am using MySQL client version: 5.1.49

Any ideas?

[r937]

SELECT data.*
  FROM data
LEFT OUTER
  JOIN data_backup
    ON data_backup.item = data.item
 WHERE data_backup.item IS NULL

[bradical1379]

Thanks for the help!

Unfortunately, the query is still crashing both ssh and phpmyadmin when I attempt to run it.

Any ideas on how I could better optimize it?

[r937]

Any ideas on how I could better optimize it?

indexes on the join columns

could you please do a SHOW CREATE TABLE for both tables and i'll show you how

[itmitică]

Instead of

SELECT *

do

SELECT id

where "id" is the primary key column. If needed, you can retrieve the full rows after that, using the "id" results set.

Or some other relevant column(s). "SELECT *" is generally a bad idea.