Issue met in doing a simple SQL query through PhpMyAdmin

Question

I created a database and two tables customer_full and customer_change through PhpMyAdmin. I successfully populated the two tables from two text files, the customer_full now contains 12902 rows while customer_change has 12947 rows, and each row has a CUSTOMER_ID field to identify each customer. The customer_change table has some new members that customer_full doesn't, and I want to capture them.

The task now I need to do is very common, I need to get the rows that only exist in the customer_change table but not exist in the customer_full table, so I wrote and executed the following query:

SELECT * FROM customer_change 
LEFT JOIN customer_full ON customer_change.CUSTOMER_ID = customer_full.ID 
WHERE customer_full.ID IS NULL

It seems that after I submit the query in the PhpMyAdmin, it will always stay at "waiting for http://localhost/phpmyadmin/import.php" and finally turn out to be a time-out error. However, if I run some simple query like

SELECT * FROM customer_change,customer_full

It will work and returning the result by simply joining every record from two tables. So I am wondering could any experts help me debug, could it be my query has a effciency issue or should I do more checking on the configuration of the PhpMyAdmin? Or could there be any other possible reasons my previous query failed?

Please pardon me if this is simple since I just began self-learning MySql. Thanks in advance for all the help.

Johan · Accepted Answer · 2011-06-27 15:45:43Z

2

This:

SELECT * FROM customer_change,customer_full

Is a cross-join it joins every row in table customer_change against every row in customer_full. So it returns 12,902 x 12,947 = 167,042,194 rows. No idea why you'd want this.

The left join you've written looks correct:

SELECT * FROM customer_change 
LEFT JOIN customer_full ON customer_change.CUSTOMER_ID = customer_full.ID 
WHERE customer_full.ID IS NULL

Missing index
In order for it to run quickly, you need to put an index on customer_change.CUSTOMER_ID and an index on customer_full.ID.
Once you've done that it should run instantly.

Corrupt tables
There's also a possibility that your tables are corrupt; esp. if you're using MyISAM.
Try and run the step outlined in this link:
http://dev.mysql.com/doc/refman/5.0/en/corrupted-myisam-tables.html
Or this link: http://www.thegeekstuff.com/2008/09/how-to-repair-corrupted-mysql-tables-using-myisamchk/

answered Jun 27, 2011 at 15:45

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Over a year ago

Thanks for the indexing part. Actually I switched from using LEFT_JOIN to NOT IN, seems to be even faster.

Johan Over a year ago

@Robert: select x from y where x not in (select ...) is much much slower 95% of the time. It's only faster in your case because there are few mismatches between the tables so the not in list is small. If the not in list gets bigger it will rapidly get much slower than the left join ... where x is null

Collectives™ on Stack Overflow

Issue met in doing a simple SQL query through PhpMyAdmin

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related