mysql优化
我有一个包含400+事件的' events‘表，其中包含以下关系-一对多与event_dates (45,000行)，一对多与event_categories (1,000行)。
如果您能改进这个冗长而缓慢的查询(我的sql技能相当差)，我将不胜感激：
SELECT
events.id AS id,
events.name AS event,
events.listing_type AS TYPE ,
GROUP_CONCAT( DISTINCT event_categories.category_id ) AS category_id,
events.location AS location,
events.summary AS summary,
counties.name AS county,
events.postcode AS postcode,
events.picture AS picture,
events.venue AS venue,
events.lat AS lat,
events.lng AS lng
FROM
EVENTS ,
event_dates,
event_categories,
counties,
categories
WHERE
events.preview =0
AND event_dates.start_date > DATE( NOW( ) )
AND events.id = event_dates.event_id
AND events.id = event_categories.event_id
AND events.county_id = counties.id
AND event_categories.category_id = categories.id
AND events.city LIKE '%London%'
GROUP BY id
ORDER BY events.id DESC编辑:这是一个事件搜索查询，需要同时按类别表和日期表进行搜索。表结构如下：
event_dates：id start_date end_date start_time end_time event_id
event_categories：id event_id category_id
回答 3
Stack Overflow用户
发布于 2010-11-22 01:20:20
我将您的查询重写为：
SELECT e.id,
e.name AS event,
e.listing_type AS TYPE ,
ec.category_id,
e.location,
e.summary,
co.name AS county,
e.postcode,
e.picture,
e.venue,
e.lat,
e.lng
FROM EVENTS e
JOIN EVENT_DATES ed ON ed.event_id = e.id
JOIN (SELECT t.event_id,
GROUP_CONCAT( DISTINCT t.category_id ) AS category_id
FROM EVENT_CATEGORIES t
GROUP BY t.event_id) ec ON ec.event_id = e.id
JOIN COUNTIES co ON c.id = e.county_id
WHERE e.preview = 0
AND ed.start_date > DATE( NOW( ) )
AND e.city LIKE '%London%'
ORDER BY e.id DESC你有：
虽然操作中的GROUP BY在valid on
- 中是有效的，但它并未用于CATEGORIES表的连接，此版本是
- LIKE，如果
EVENTS.city(IE：‘%LONDON%’)上有索引，则左侧带有通配符的LIKE将无法使用索引
建议/建议
- 索引连接条件列(如果它们尚未建立)，然后查看WHERE子句
中的列索引
Stack Overflow用户
发布于 2010-11-22 01:21:54
我认为您应该在更小的范围内执行GROUP_CONCAT操作(作为FROM子句中的子查询)，这可以很好地提高整体性能。从查询中删除表categories也是如此，因为它只会造成混淆(除非您的表处于语义不完整的状态，以至于在event_categories中有没有出现在定义类别表中的类别id值)。
这些观察结果导致：
SELECT e.id AS id,
e.name AS event,
e.listing_type AS type,
cat.category_id AS category_id,
e.location AS location,
e.summary AS summary,
counties.name AS county,
e.postcode AS postcode,
e.picture AS picture,
e.venue AS venue,
e.lat AS lat,
e.lng AS lng
FROM Events AS e
JOIN event_dates AS d ON e.id = d.id AND d.start_date > DATE(NOW())
JOIN (SELECT id, GROUP_CONCAT(category_id) AS category_id
FROM event_categories
GROUP BY id
) AS cat ON e.id = cat.id
JOIN counties ON e.county_id = counties.id
WHERE e.preview = 0
AND e.city LIKE '%London%'
ORDER BY events.id DESC如果查询中确实需要categories表，则它属于子查询：
SELECT e.id AS id,
e.name AS event,
e.listing_type AS type,
cat.category_id AS category_id,
e.location AS location,
e.summary AS summary,
counties.name AS county,
e.postcode AS postcode,
e.picture AS picture,
e.venue AS venue,
e.lat AS lat,
e.lng AS lng
FROM Events AS e
JOIN event_dates AS d ON e.id = d.id AND d.start_date > DATE(NOW())
JOIN (SELECT id, GROUP_CONCAT(category_id) AS category_id
FROM event_categories AS ec
JOIN categories AS ct ON ec.category_id = ct.category_id
GROUP BY id
) AS cat ON e.id = cat.id
JOIN counties ON e.county_id = counties.id
WHERE e.preview = 0
AND e.city LIKE '%London%'
ORDER BY events.id DESCStack Overflow用户
发布于 2010-11-22 00:58:06
首先，去掉AND events.city LIKE '%London%'，让你的定位依赖于county_id (我希望它是一个外键)
https://stackoverflow.com/questions/4238996
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号