SELECT COUNT(cc.Id) FROM tCardConsumeHistory cc,tCinema c,tCity t ,tCardJifen j WHERE cc.cinemaId=c.ID AND t.ID=c.CityID AND j.CardNum=cc.CardNum AND t.id IN(10) AND cc.Date BETWEEN '2013-11-29' AND '2014-01-13 23:59:59'SELECT COUNT(cc.id) FROM (SELECT cc.id FROM tCardConsumeHistory cc WHERE DATE BETWEEN '2013-11-29' AND '2014-01-10 23:59:59')cc,tCinema c,tCity t,tCardJifen j WHERE cc.cinemaId=c.ID AND t.ID=c.CityID AND j.CardNum=cc.CardNum AND t.id IN(10)DATE 上建有索引上面哪个效率更高呢
EXPLAIN 第一个结果
id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE t const PRIMARY PRIMARY 4 const 1 Using index1 SIMPLE cc ALL CardNum,date \N \N \N 443926 Using where1 SIMPLE c eq_ref PRIMARY PRIMARY 4 cc.cinemaId 1 Using where1 SIMPLE j eq_ref CardNum CardNum 152 cc.CardNum 1 Using indexEXPLAIN 第二个结果
id select_type table type possible_keys key key_len ref rows Extra1 PRIMARY t const PRIMARY PRIMARY 4 const 1 Using index1 PRIMARY <derived2> ALL \N \N \N \N 24298 1 PRIMARY c eq_ref PRIMARY PRIMARY 4 cc.cinemaId 1 Using where1 PRIMARY j eq_ref CardNum CardNum 152 cc.CardNum 1 Using index2 DERIVED cc range date date 4 \N 46468 Using whereEXPLAIN结果到底怎么看,主要看哪几个关键字段???
EXPLAIN 第一个结果
id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE t const PRIMARY PRIMARY 4 const 1 Using index1 SIMPLE cc ALL CardNum,date \N \N \N 443926 Using where1 SIMPLE c eq_ref PRIMARY PRIMARY 4 cc.cinemaId 1 Using where1 SIMPLE j eq_ref CardNum CardNum 152 cc.CardNum 1 Using indexEXPLAIN 第二个结果
id select_type table type possible_keys key key_len ref rows Extra1 PRIMARY t const PRIMARY PRIMARY 4 const 1 Using index1 PRIMARY <derived2> ALL \N \N \N \N 24298 1 PRIMARY c eq_ref PRIMARY PRIMARY 4 cc.cinemaId 1 Using where1 PRIMARY j eq_ref CardNum CardNum 152 cc.CardNum 1 Using index2 DERIVED cc range date date 4 \N 46468 Using whereEXPLAIN结果到底怎么看,主要看哪几个关键字段???
解决方案 »
- left join 问题
- linux下启动mysql出现问题
- can't not update table 'szs_pick' in stored function/trigger because it is alrea
- mysql简单赋值错误
- 动态sql只能写在存储过程吗?怎么返回结果集
- 如何将MYSQL 数据库 转成 SQL SERVER数据库??
- 这样的mysql语句怎么写?急!
- 如何汇总非数字型的字段?
- MYSQL多表查询慢
- 多个线程load infile插入表,同时select count(1)此表,导致统计一直处于sending data长达好几千秒,如何解决
- 这个sql语句很难啊,求高手解答
- 请教关于 InnoDB 和 MyISAM 的选择?拜谢各位大神了!
BETWEEN '2013-11-29' AND '2014-01-13 23:59:59'2.SELECT COUNT(cc.id) FROM (SELECT cc.id,cc.CardNum, cc.Date,cc.cinemaId FROM
tCardConsumeHistory cc WHERE DATE BETWEEN '2014-01-01' AND '2014-01-10 23:59:59')cc,tCinema c,tCity t,tCardJifen j
WHERE cc.cinemaId=c.ID AND t.ID=c.CityID AND j.CardNum=cc.CardNum AND t.id IN(10)第一个explain第二个: