一直对索引没研究,最近在一个查询的时候花了0.600秒,比平时的慢了许多才关注此问题 。
table1
id shopname images images_display
1 lenova 1.jpg true
2 lenova 2.jpg true
3 lenova 3.jpg true
4 lenova 4.jpg true
5 sumsung 1.jpg true
6 sumsung 2.jpg true
7 sumsung 3.jpg true
8 sumsung 4.jpg false
9 sony 1.jpg false
10 sony 2.jpg true
11 sony 3.jpg true
12 sony 4.jpg truetable 2
id shopname viewcount new_arrived
1 lenova 100 12/5/2013
2 sony 105 11/5/2013
3 sumsung 110 10/5/2013
4 apple 150 15/5/2013
5 puma 200 10/5/2013条件, table1 和 table2 相同的shopname,images_display ='true' 取2张最新的images group在一起
结果 :
id shopname viewcount new_arrived images
1 lenova 100 12/5/2013 3.jpg,4.jpg
5 sumsung 110 10/5/2013 2.jpg,3.jpg
9 sony 105 11/5/2013 3.jpg,4.jpg上面只是例子,实际上 table1 的数据大概有几万条,1 间店有几百张images ,table 2 则有 200-300 间shop
下面是我的语句,写的很慢...希望高人指导... 我一向来都不会使用索引,希望高人可以顺便提点一下 .. 感激不敬 !select z.id,z.shopname,z.viewcount,z.new_arrived,group_concat(z.images),
from (select * from (select a.id,a.shopname,a.images,b.new_arrived,b.viewcount from
(select id,shopname,images from table1
where images_display= 'true')a
inner join table2 b on a.shopname = b.shopname)c where not exists (select * from (select a.id,a.shopname,a.images,
b.new_arrived,b.viewcount
from (select id,shopname,images from table1
where images_display = 'true')a
inner join table2 b on a.shopname = b.shopname)x where x.shopname = c.shopname and x.id > c.id+1))z group by z.shopname
table1
id shopname images images_display
1 lenova 1.jpg true
2 lenova 2.jpg true
3 lenova 3.jpg true
4 lenova 4.jpg true
5 sumsung 1.jpg true
6 sumsung 2.jpg true
7 sumsung 3.jpg true
8 sumsung 4.jpg false
9 sony 1.jpg false
10 sony 2.jpg true
11 sony 3.jpg true
12 sony 4.jpg truetable 2
id shopname viewcount new_arrived
1 lenova 100 12/5/2013
2 sony 105 11/5/2013
3 sumsung 110 10/5/2013
4 apple 150 15/5/2013
5 puma 200 10/5/2013条件, table1 和 table2 相同的shopname,images_display ='true' 取2张最新的images group在一起
结果 :
id shopname viewcount new_arrived images
1 lenova 100 12/5/2013 3.jpg,4.jpg
5 sumsung 110 10/5/2013 2.jpg,3.jpg
9 sony 105 11/5/2013 3.jpg,4.jpg上面只是例子,实际上 table1 的数据大概有几万条,1 间店有几百张images ,table 2 则有 200-300 间shop
下面是我的语句,写的很慢...希望高人指导... 我一向来都不会使用索引,希望高人可以顺便提点一下 .. 感激不敬 !select z.id,z.shopname,z.viewcount,z.new_arrived,group_concat(z.images),
from (select * from (select a.id,a.shopname,a.images,b.new_arrived,b.viewcount from
(select id,shopname,images from table1
where images_display= 'true')a
inner join table2 b on a.shopname = b.shopname)c where not exists (select * from (select a.id,a.shopname,a.images,
b.new_arrived,b.viewcount
from (select id,shopname,images from table1
where images_display = 'true')a
inner join table2 b on a.shopname = b.shopname)x where x.shopname = c.shopname and x.id > c.id+1))z group by z.shopname
解决方案 »
- postgresql 中的存储语句对应的查询语句
- my数据库同步问题
- 请教个关于mysql语句里面日期相减的问题
- windows下如何实现mysql的自动备份?
- 请教怎样把SQL2000数据库导出成.csv文件????请问详细步骤,而且不要丢数据~~~
- mysql怎么可以这样啊
- 对MYSQL进行LOAD DATA INFILE '' INTO DATABASE能否在客户端程序中处理?
- 求优化,排行榜功能.
- 求助!请问mysql如何快速导入超大的sql文件?
- MySQL新版Sysbench测试每秒达50万查询
- redhat 9.0中安装mysql: arguments to --root begin with /a
- 需要大量在MySQL数据库中实现独立于操作系统的目录(或者说树)的资料
ALTER TABLE table1 ADD INDEX syshopname ( shopname(100) )
还是一样耶,而且explain 来看
possiable key , ,key , key_len , ref , 都是 null .. 好像没用到..
select *
from
(
select shopname,group_concat(a.images separator ',') as images
from table1 a
where images_display=true
and 2>(select count(B.ID) from table1 b where a.shopname=b.shopname and a.id<b.id AND images_display=true )
group by a.shopname) a inner join table2 b on a.shopname=b.shopname
from table1 a
join table2 b on a.shopname =b.shopname
where (select count(*) from table1 where shopname = a.shopname and id > a.id and images_display = 'true' ) < 2 and images_display = 'true' group by a.shopname
;shopname 加个索引