mysql - Order by 使 mysql 变慢

Question

我们花了 5 秒来加载，但是当我删除 ORDER BY 时，我们只花了 0.4 秒，有什么改进这个查询的想法吗？

下面的查询也有更多的左连接，但不认为这不是问题

SELECT b.id, 
  b.name, 
  b.start_date, 
  b.start_time, 
  at.filename, 
  at.width, 
  at.height, 
  at.id   AS attachment_id, 
  ve.name AS venue_name, 
  ve.slug AS v_slug, 
  ve.address, 
  ci.name AS city_name, 
  st.code AS state_code 
FROM   events b 
LEFT JOIN attachments at 
    ON at.foreign_id = b.id 
LEFT JOIN venues ve 
    ON b.venue_id = ve.id 
LEFT JOIN cities ci 
    ON ve.city_id = ci.id 
LEFT JOIN states st 
    ON ci.state_id = st.id 
WHERE  1 = 1 
  AND b.start_date >= '2016-10-06' 
  AND b.is_active = 1 
  AND b.virtual_delete = 0 
  AND ve.is_approved = 1 
GROUP  BY b.id 
ORDER  BY b.event_view_count DESC 
LIMIT  30

Answer 1

请引用this article .

To increase ORDER BY speed, check whether you can get MySQL to use indexes rather than an extra sorting phase.

因此，首先检查 event_view_count 列是否已编入索引。