Large query looking for differences in tables causing mysql to crash

Databases
1

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?

2 

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

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?

4

indexes on the join columns

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

5

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.