mysql - 我应该为这个臃肿的查询添加哪些 SQL 索引？

Question

我想知道索引是否会加快查询速度。我上次检查需要9秒。流量表有大约 30 万行，列表和用户 5 千行。如果这只是一个蹩脚的查询，我也愿意接受 mock /羞辱。我早就写过了。

它应该获得页面浏览量(流量)最多的列表。如果缺少解释，请告诉我。

SELECT traffic_listingid AS listing_id, 
       COUNT(traffic_listingid) AS genuine_hits, 
       COUNT(DISTINCT traffic_ipaddress) AS distinct_ips, 
       users.username, 
       listings.listing_address,
       listings.datetime_created,
       DATEDIFF(NOW(), listings.datetime_created) AS listing_age_days 
FROM traffic 
  LEFT JOIN listings 
    ON traffic.traffic_listingid = listings.listing_id 
  LEFT JOIN users 
    ON users.id = listings.seller_id 
WHERE traffic_genuine = 1 
  AND listing_id IS NOT NULL 
  AND username IS NOT NULL 
  AND DATEDIFF(NOW(), traffic_timestamp) < 24 
GROUP BY traffic_listingid 
ORDER BY distinct_ips DESC
LIMIT 10

附言

ENGINE=MyISAM /
MySQL Server 4.3

Answer 1

旁注:

1.你有

  LEFT JOIN listings 
    ON traffic.traffic_listingid = listings.listing_id 
  ...
WHERE ... 
  AND listing_id IS NOT NULL 

此条件取消 LEFT JOIN。将您的查询更改为:

  INNER JOIN listings 
    ON traffic.traffic_listingid = listings.listing_id 

并从 WHERE 条件中删除 listing_id IS NOT NULL。

同样的事情适用于 LEFT JOIN user 和 username IS NOT NULL。

---

2.traffic_timestamp检查:

      DATEDIFF(NOW(), traffic_timestamp) < 24

使索引难以使用。改成这样可以使用索引
(并且检查我的版本是否相同，我可能有错误):

      traffic_timestamp >= CURRENT_DATE() - INTERVAL 23 DAY

---

3.COUNT(non-nullable-column)等同于COUNT(*)。更改:

   COUNT(traffic_listingid) AS genuine_hits, 

到:

   COUNT(*) AS genuine_hits, 

因为它在 MySQL 中要快一点(尽管我不确定 4.3 版是否如此)

---

对于索引问题，您应该在用于连接的每一列上至少有一个索引。为 traffic_timestamp 添加一个可能也有帮助。

如果您告诉我们 traffic_ipaddress 和 traffic_timestamp 在哪些表中，以及 EXPLAIN EXTENDED 显示什么，有人可能会有更好的主意.

再次阅读查询，似乎它实际上是 GROUP BY 仅在表 traffic 中，其他 2 个表用于获取引用数据。所以，这个查询相当于一个(traffic group by)-join-listing-join-user。不确定这对您的 MySQL 旧版本是否有帮助，但如果一个查询在您的系统中运行得更快，那么同时拥有两个版本的查询和测试可能会更好。