SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162
查询二:
SELECT id,user_id,quantity ,(select count(id) FROM coupon where order_id=`order`.id) as sumq FROM `order` where `team_id`=162
这两个查询都很慢
查询结果是120条
表order约有4万条数据,表coupon约有5万条数据
14 个解决方案
#1
在coupon的order_id上创建索引
在order的order_id上创建索引
在order的team_id上创建索引
在order的order_id上创建索引
在order的team_id上创建索引
#2
`order`.team_id,`order`.id 上建立复合索引,要是order.id是主键了,则只需要在order.team_id上建立索引
coupon.order_id 建立索引
coupon.order_id 建立索引
#3
为什么慢?
#4
存储引擎用MyISAM需要花1分以上的时间
用InnoDB要5-6秒
用InnoDB要5-6秒
#5
explain看执行计划
#6
贴出你的
explain SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162
explain SELECT id,user_id,quantity ,(select count(id) FROM coupon where order_id=`order`.id) as sumq FROM `order` where `team_id`=162
另外用 show index from tabl1 命令把相关表的索引情况贴出来以供分析。
explain SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162
explain SELECT id,user_id,quantity ,(select count(id) FROM coupon where order_id=`order`.id) as sumq FROM `order` where `team_id`=162
另外用 show index from tabl1 命令把相关表的索引情况贴出来以供分析。
#7
#8
这些是优化查询的基本信息,都要贴出来。
#9
没有索引的,建立team_id、order_id组合做引,然后explain 2个sql语句,看看走没有走索引。
#10
以下3条语句是在InnoDB存储引擎下执行
SELECT
id,
user_id,
quantity,
(SELECT
COUNT(id)
FROM coupon
WHERE order_id = `order`.id) AS countq
FROM `order`
WHERE `team_id` = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
用时3.594秒
SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`,
coupon
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
AND `order`.id = coupon.order_id
GROUP BY `order`.id
用时0.296秒
SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`
LEFT JOIN coupon
ON (`order`.id = coupon.order_id)
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
GROUP BY `order`.id
用时3.406秒
SELECT
id,
user_id,
quantity,
(SELECT
COUNT(id)
FROM coupon
WHERE order_id = `order`.id) AS countq
FROM `order`
WHERE `team_id` = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
用时3.594秒
SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`,
coupon
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
AND `order`.id = coupon.order_id
GROUP BY `order`.id
用时0.296秒
SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`
LEFT JOIN coupon
ON (`order`.id = coupon.order_id)
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
GROUP BY `order`.id
用时3.406秒
#11
在MyISAM整个没有办法接受
#12
EXPLAIN SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`,
coupon
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
AND `order`.id = coupon.order_id
GROUP BY `order`.id;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE coupon ALL 48154 Using temporary; Using filesort
1 SIMPLE order eq_ref PRIMARY PRIMARY 8 zuituan.coupon.order_id 1 Using where
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`,
coupon
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
AND `order`.id = coupon.order_id
GROUP BY `order`.id;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE coupon ALL 48154 Using temporary; Using filesort
1 SIMPLE order eq_ref PRIMARY PRIMARY 8 zuituan.coupon.order_id 1 Using where
#13
coupon.order_id 这个字段上建立索引再看看explain;
#14
楼主似乎并不认真看别人的回复!
你的show index 都贴在哪儿了?
从你的EXPLAIN来看,你的表中似乎根本没有创建索引。
#1
在coupon的order_id上创建索引
在order的order_id上创建索引
在order的team_id上创建索引
在order的order_id上创建索引
在order的team_id上创建索引
#2
`order`.team_id,`order`.id 上建立复合索引,要是order.id是主键了,则只需要在order.team_id上建立索引
coupon.order_id 建立索引
coupon.order_id 建立索引
#3
为什么慢?
#4
存储引擎用MyISAM需要花1分以上的时间
用InnoDB要5-6秒
用InnoDB要5-6秒
#5
explain看执行计划
#6
贴出你的
explain SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162
explain SELECT id,user_id,quantity ,(select count(id) FROM coupon where order_id=`order`.id) as sumq FROM `order` where `team_id`=162
另外用 show index from tabl1 命令把相关表的索引情况贴出来以供分析。
explain SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162
explain SELECT id,user_id,quantity ,(select count(id) FROM coupon where order_id=`order`.id) as sumq FROM `order` where `team_id`=162
另外用 show index from tabl1 命令把相关表的索引情况贴出来以供分析。
#7
#8
这些是优化查询的基本信息,都要贴出来。
#9
没有索引的,建立team_id、order_id组合做引,然后explain 2个sql语句,看看走没有走索引。
#10
以下3条语句是在InnoDB存储引擎下执行
SELECT
id,
user_id,
quantity,
(SELECT
COUNT(id)
FROM coupon
WHERE order_id = `order`.id) AS countq
FROM `order`
WHERE `team_id` = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
用时3.594秒
SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`,
coupon
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
AND `order`.id = coupon.order_id
GROUP BY `order`.id
用时0.296秒
SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`
LEFT JOIN coupon
ON (`order`.id = coupon.order_id)
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
GROUP BY `order`.id
用时3.406秒
SELECT
id,
user_id,
quantity,
(SELECT
COUNT(id)
FROM coupon
WHERE order_id = `order`.id) AS countq
FROM `order`
WHERE `team_id` = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
用时3.594秒
SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`,
coupon
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
AND `order`.id = coupon.order_id
GROUP BY `order`.id
用时0.296秒
SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`
LEFT JOIN coupon
ON (`order`.id = coupon.order_id)
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
GROUP BY `order`.id
用时3.406秒
#11
在MyISAM整个没有办法接受
#12
EXPLAIN SELECT
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`,
coupon
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
AND `order`.id = coupon.order_id
GROUP BY `order`.id;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE coupon ALL 48154 Using temporary; Using filesort
1 SIMPLE order eq_ref PRIMARY PRIMARY 8 zuituan.coupon.order_id 1 Using where
`order`.id,
`order`.quantity,
COUNT(coupon.id)
FROM `order`,
coupon
WHERE `order`.team_id = 162
AND `order`.state = 'pay'
AND `order`.delivery = 'coupon'
AND `order`.id = coupon.order_id
GROUP BY `order`.id;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE coupon ALL 48154 Using temporary; Using filesort
1 SIMPLE order eq_ref PRIMARY PRIMARY 8 zuituan.coupon.order_id 1 Using where
#13
coupon.order_id 这个字段上建立索引再看看explain;
#14
楼主似乎并不认真看别人的回复!
你的show index 都贴在哪儿了?
从你的EXPLAIN来看,你的表中似乎根本没有创建索引。