加快MySQL加入检查重复 [英] Speed up MySQL join to check for duplicates
问题描述
我使用以下查询返回所有具有相同名称和重要名称的重复记录。诀窍是,contact_id必须是降序。
I'm using the following query to return all duplicate records with the same first and last name. The trick is that the contact_id, has to be in descending order.
查询按预期返回联系人,但它只是SO SLOW!在检查大约30,000条记录的时候,大约需要6-8秒。
The query returns the contacts as expected, but it is just SO SLOW! Takes about 6-8 seconds when checking around 30,000 records.
我有contact_firstName,contact_lastName,contact_client_id和contact_id都在数据库中索引。
I have the contact_firstName, contact_lastName, contact_client_id, and contact_id all indexed in the database.
任何想法我可以做些什么来尝试加速一点?感谢您的帮助:)
Any ideas what I could do to try and speed this up a bit? Thanks for your help :)
SELECT z.contact_id, z.contact_firstName, z.contact_lastName, RIGHT(z.contact_lastName,1) AS nameNum
FROM (`contacts` x)
JOIN `contacts` z ON `x`.`contact_firstName` = `z`.`contact_firstName`
AND x.contact_lastName = z.contact_lastName
AND x.contact_client_id = ".$ID."
AND z.contact_client_id = ".$ID."
WHERE `x`.`contact_id` < `z`.`contact_id`
GROUP BY `z`.`contact_id`
推荐答案
不作任何承诺,但这是替代尝试:
Not making any promises, but here's an alternative to try:
SELECT c.contact_id, c.contact_firstName, c.contact_lastName, RIGHT(c.contact_lastName,1) AS nameNum
FROM (SELECT contact_firstName, contact_lastName, MIN(contact_id) AS MinID
FROM contacts
WHERE contact_client_id = ".$ID."
GROUP BY contact_firstName, contact_lastName
HAVING COUNT(*) > 1) t
INNER JOIN contacts c
ON t.contact_firstName = c.contact_firstName
AND t.contact_lastName = c.contact_lastName
AND c.contact_client_id = ".$ID."
AND t.MinID <> c.contact_id
这篇关于加快MySQL加入检查重复的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!