MYSQL Stuck 生成临时表(大量查询) [英] MYSQL Stuck Generating temp table (massive query)

问题描述

我有 4 张桌子(一对多):不要谈论电子邮件"关系.这是我的开发者老板多年前建立的方式.

I have 4 tables (1 to many): Dont say anything about that "email" relation. It is how my developer boss built it years ago.

EMPLOYEES (+-50 results)
------------------------------------------------
id    name    
1     EmpName 1
2     EmpName 2  

CUSTOMERS (+50k results)
------------------------------------------------
id    name    email            employee_assigned
1     John    [e-mail]     12
2     Donald  [e-mail]  6

INTERESTS_CATEGORIES (+650k results)
------------------------------------------------
id    customer_email    category_id
1     [e-mail]      97
2     [e-mail]      13
3     [e-mail]   56
4     [e-mail]   126
5     [e-mail]   45

INTERESTS_PRODUCTS (+650k results)
------------------------------------------------
id    customer_email    product_id
1     [e-mail]      78
2     [e-mail]      23
3     [e-mail]   19
4     [e-mail]   56
5     [e-mail]   45

所以我需要按客户分配的员工和他们的兴趣来过滤客户.这是查询:

So I need to filter the customers by their assigned employee and their interests. And here is the query:

SELECT
    *
FROM          
(
    SELECT 
        customers.id                        AS 'id',
        customers.name                      AS 'first_name',
        customers.email,
        employees.id                        AS 'employee_id'
    FROM 
        customers, 
        employees
    WHERE 
        employees.id = 2
        AND 
        customers.employee_assigned = employees.id
) AS myCustomers

LEFT JOIN interests_categories 
    ON interests_categories.customer_email = myCustomers.email
LEFT JOIN interests_products 
    ON interests_categories.customer_email = myCustomers.email
WHERE       
( 
    interests_categories.category_id = 20
    OR 
    interests_categories.category_id = 21
)

GROUP BY myCustomers.email

所以，问题是:

• 如果员工分配的客户数量较少(如 3 个)查询成功.

• If the employee has a low number of assigned customers (like 3) query is successfull.

如果员工有中等数量的指定客户(超过 100 个)，查询会卡住.

我执行了 SHOW PROCESSLIST，它被卡住了生成临时表".

I execute SHOW PROCESSLIST and it is stucked "Generating temp table".

有人知道吗?:(谢谢.

Anyone has idea? :( Thank you.

推荐答案

检查表上的索引并尝试以下操作:

Check the indexes on your tables and try this:

SELECT 
    c.id AS 'id',
    c.name AS 'first_name',
    c.email,
    e.id AS 'employee_id'
    ic.*,
    ip.*
FROM customers c
JOIN employees e
ON c.employee_assigned = e.id
LEFT JOIN interests_categories ic
    ON ic.customer_email = c.email
LEFT JOIN interests_products ip
    ON ic.customer_email = c.email
WHERE      
( 
    ic.category_id IN (20,21)
    AND e.id = 2
)
GROUP BY myCustomers.email

这篇关于MYSQL Stuck 生成临时表(大量查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！