php - 如何以最短的运行时间执行sql select查询

Question

我想从表中进行选择，然后对每个表进行选择。

表格:

category
+====+=======+
| id | title |
+====+=======+
this table has list of category

email
+====+=======+==========+=============+
| id | eMail | domainId | elseColumns |
+====+=======+==========+=============+
this table has list of emails but domains are in another table

domain
+====+========+=============+
| id | domain | elseColumns |
+====+========+=============+
list of domains which used in email

subscriber_category
+========+============+
| userId | categoryId |
+========+============+
list of emails in categories

现在的问题是我如何以最短的运行时间列出类别和其中的电子邮件数量？我的尝试是等待 20 秒，等待 200000 封电子邮件和 20 个类别。

sql:

SELECT category.*,
(SELECT COUNT(DISTINCT subscriber_category.userId) FROM subscriber_category
    JOIN email ON email.id=subscriber_category.userId
    JOIN domain ON domain.id=email.domainId
WHERE subscriber_category.categoryId=category.id
    AND email.blackList=0
    AND domain.blackList=0
) AS qty
FROM category WHERE category.userId=1 ORDER BY category.title ASC

Answer 1

这是您的查询:

SELECT c.*,
       (SELECT COUNT(DISTINCT sc.userId)
        FROM subscriber_category sc JOIN
             email e
             ON e.id = sc.userId JOIN
             domain d
             ON d.id = e.domainId
       WHERE sc.categoryId = c.id AND e.blackList = 0 AND d.blackList = 0
      ) AS qty
FROM category c
WHERE c.userId = 1
ORDER BY c.title ASC;

结构相当合理，索引应该有助于性能。第一个索引位于category(userId, title, id)。该索引应该用于WHERE子句、ORDER BY和相关子查询。

接下来，我假设您在 email 和 domain 中的 id 列上有索引。如果您将 blacklist 标志包含为索引中的第二列，则可以使这些稍微更适用于查询。更重要的是，您需要 subscriber_category(categoryId, userId) 上的索引。如果没有必要，我还建议删除 count(distinct)。