这是整个查询。。。
SELECT s.*, (SELECT url FROM show_medias WHERE show_id = s.id AND is_primary = 1) AS media_url
FROM (shows As s)
WHERE `s`.`id` IN (
SELECT DISTINCT st.show_id
FROM show_time_schedules AS sts
LEFT JOIN show_times AS st ON st.id = sts.show_time_id
WHERE sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date)
)
AND `s`.`is_active` = 1
ORDER BY s.name asc
如果。。。
SELECT url FROM show_medias WHERE show_id = s.id AND is_primary = 1
(0.0004 sec)
而且。。。
SELECT DISTINCT st.show_id
FROM show_time_schedules AS sts
LEFT JOIN show_times AS st ON st.id = sts.show_time_id
WHERE sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date)
(0.0061 sec)
有明显的原因吗。。。。
SELECT s.*, (inner query 1) AS media_url
FROM (shows As s)
WHERE `s`.`id` IN ( inner query 2 )
AND `s`.`is_active` = 1
ORDER BY s.name asc
正在服用5.7245 sec
?
解释扩展
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY s ALL NULL NULL NULL NULL 151 100.00 Using where; Using filesort
3 DEPENDENT SUBQUERY sts ALL NULL NULL NULL NULL 26290 100.00 Using where; Using temporary
3 DEPENDENT SUBQUERY st eq_ref PRIMARY PRIMARY 4 bvcdb.sts.show_time_id 1 100.00 Using where
2 DEPENDENT SUBQUERY show_medias ALL NULL NULL NULL NULL 159 100.00 Using where
您可以始终使用EXPLAIN或EXPLAIN EXTENDED来查看MySql对查询所做的操作
您也可以用稍微不同的方式编写查询,您尝试过以下方法吗?
SELECT s.*,
sm.url AS media_url
FROM shows AS s
INNER JOIN show_medias AS sm ON s.id = SM.show_id
WHERE `s`.`id` IN (
SELECT DISTINCT st.show_id
FROM show_time_schedules AS sts
LEFT JOIN show_times AS st ON st.id = sts.show_time_id
WHERE sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date)
)
AND `s`.`is_active` = 1
AND sm.is_primary = 1
ORDER BY s.name asc
看看它的效果会很有趣。我希望它会更快,因为目前,我认为MySql将为您的每个节目运行内部查询1(这样一个查询将运行多次。联接应该更高效。)
如果您想要所有在show_medias中没有行的节目,请将INNER JOIN替换为LEFT JOIN。
编辑:
我很快就会看看你的EXPLAIN EXTENDED,我也不知道你是否想尝试以下内容;它删除了所有的子查询:
SELECT DISTINCT s.*,
sm.url AS media_url
FROM shows AS s
INNER JOIN show_medias AS sm ON s.id = SM.show_id
INNER JOIN show_times AS st ON (s.id = st.show_id)
RIGHT JOIN show_time_schedules AS sts ON (st.id = sts.show_time_id)
WHERE `s`.`is_active` = 1
AND sm.is_primary = 1
AND sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date)
ORDER BY s.name asc
(如果能看到EXPLAIN EXTENDED也很好——你可以把它添加到这篇文章的评论中)。
进一步编辑:
在您的EXPLAIN EXTENDED(如何阅读这些内容的良好开端在这里)
USING FILESORT和USING TEMPORARY都是关键指标。希望我推荐的第二个查询应该删除(子查询中的)任何TEMPORARY表。然后尝试关闭ORDER BY,看看这是否有影响(我们可以将其添加到目前的发现中:-)
我还可以看到,查询可能会错过许多索引查找;您的所有id列都是索引匹配的主要候选列(通常需要注意索引)。我也会尝试添加这些索引,然后再次运行EXPLAIN EXTENDED,看看现在有什么不同(编辑,我们已经从上面的评论中知道了!)
CTE解决方案来了:(我的错,mysql没有CTE,但问题太普遍了)
WITH RECURSIVE tree AS (
SELECT t0.id
, t0.study_start_time
, t0.study_end_time
FROM tab t0
WHERE NOT EXISTS (SELECT * FROM tab nx
WHERE nx.id=t0.id
AND nx.study_end_time = t0.study_start_time
)
UNION
SELECT tt.id
,tt.study_start_time
,t1.study_end_time
FROM tab t1
JOIN tree tt ON t1.id=tt.id
AND t1.study_start_time = tt.study_end_time
)
SELECT * FROM tree
WHERE NOT EXISTS (SELECT * FROM tab nx
WHERE nx.id=tree.id
AND tree.study_end_time = nx.study_start_time
)
ORDER BY id
;
结果:
CREATE TABLE
INSERT 0 15
id | study_start_time | study_end_time
------+------------------+----------------
1234 | 168 | 480
2345 | 175 | 233
2345 | 400 | 425
4567 | 200 | 225
4567 | 250 | 289
4567 | 300 | 310
4567 | 320 | 340
4567 | 360 | 390
(8 rows)
QUERY计划(添加明显PK和索引后):
DROP TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tab_pkey" for table "tab"
CREATE TABLE
CREATE INDEX
INSERT 0 15
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Merge Anti Join (cost=16209.59..16292.13 rows=6386 width=12) (actual time=0.189..0.193 rows=8 loops=1)
Merge Cond: ((tree.id = nx.id) AND (tree.study_end_time = nx.study_start_time))
CTE tree
-> Recursive Union (cost=0.00..15348.09 rows=8515 width=12) (actual time=0.022..0.136 rows=15 loops=1)
-> Merge Anti Join (cost=0.00..175.04 rows=1455 width=12) (actual time=0.019..0.041 rows=8 loops=1)
Merge Cond: ((t0.id = nx.id) AND (t0.study_start_time = nx.study_end_time))
-> Index Scan using tab_pkey on tab t0 (cost=0.00..77.35 rows=1940 width=12) (actual time=0.010..0.018 rows=15 loops=1)
-> Index Scan using sssss on tab nx (cost=0.00..77.35 rows=1940 width=8) (actual time=0.003..0.008 rows=14 loops=1)
-> Merge Join (cost=1297.04..1500.28 rows=706 width=12) (actual time=0.010..0.012 rows=1 loops=6)
Merge Cond: ((t1.id = tt.id) AND (t1.study_start_time = tt.study_end_time))
-> Index Scan using tab_pkey on tab t1 (cost=0.00..77.35 rows=1940 width=12) (actual time=0.001..0.004 rows=9 loops=6)
-> Sort (cost=1297.04..1333.42 rows=14550 width=12) (actual time=0.006..0.006 rows=2 loops=6)
Sort Key: tt.id, tt.study_end_time
Sort Method: quicksort Memory: 25kB
-> WorkTable Scan on tree tt (cost=0.00..291.00 rows=14550 width=12) (actual time=0.000..0.001 rows=2 loops=6)
-> Sort (cost=726.15..747.44 rows=8515 width=12) (actual time=0.166..0.169 rows=15 loops=1)
Sort Key: tree.id, tree.study_end_time
Sort Method: quicksort Memory: 25kB
-> CTE Scan on tree (cost=0.00..170.30 rows=8515 width=12) (actual time=0.025..0.149 rows=15 loops=1)
-> Sort (cost=135.34..140.19 rows=1940 width=8) (actual time=0.018..0.018 rows=15 loops=1)
Sort Key: nx.id, nx.study_start_time
Sort Method: quicksort Memory: 25kB
-> Seq Scan on tab nx (cost=0.00..29.40 rows=1940 width=8) (actual time=0.003..0.004 rows=15 loops=1)
Total runtime: 0.454 ms
(24 rows)