通过PhpMyAdmin执行简单的SQL查询时遇到问题

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.

我通过PhpMyAdmin创建了一个数据库和两个表customer_full和customer_change。我成功地从两个文本文件填充了两个表,customer_full现在包含12902行,而customer_change有12947行,每行有一个CUSTOMER_ID字段来标识每个客户。 customer_change表有一些customer_full没有的新成员,我想捕获它们。

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:

现在我需要做的任务非常普遍,我需要获取仅存在于customer_change表中但不存在于customer_full表中的行,因此我编写并执行了以下查询:

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

似乎在我在PhpMyAdmin中提交查询后,它将始终保持“等待http://localhost/phpmyadmin/import.php”,最后结果是超时错误。但是,如果我运行一些简单的查询,如

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?

它将工作并通过简单地连接两个表中的每个记录来返回结果。所以我想知道任何专家可以帮我调试,可能是我的查询有效率问题还是我应该更多地检查PhpMyAdmin的配置?或者我之前的查询失败还有其他可能的原因吗?

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

请原谅我,如果这很简单,因为我刚开始自学MySql。在此先感谢您的帮助。

1 个解决方案

#1

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.

是一个交叉连接,它将表customer_change中的每一行连接到customer_full中的每一行。所以它返回12,902 x 12,947 = 167,042,194行。不知道你为什么要这个。

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.

缺少索引为了使其快速运行,您需要在customer_change.CUSTOMER_ID上添加索引,在customer_full.ID上添加索引。一旦你完成它,它应该立即运行。

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/

腐败的桌子你的桌子也有可能腐败; ESP。如果你正在使用MyISAM。尝试并运行此链接中列出的步骤:http://dev.mysql.com/doc/refman/5.0/en/corrupted-myisam-tables.html或者链接:http://www.thegeekstuff.com/2008 / 09 /如何对修复遭破坏的MySQL的桌 - 使用 - myisamchk的/

#1