我有两张表一张为 shop 字段有 shopid shopname cityid price一张为 shop_has_categories 存储的是商店的分类,1个商店有可能有多个分类,字段:shopid catidshop有很多,每个商店都有可能有1个或者多个分类我现在要查 cityid = 5 , price > 100 and price < 500的 catid = 10 的商店请问这样搜索效率会怎么样?
调试欢乐多
shop_has_categories 表主键为shopid,catid.索引1为shopid,索引2为catid.
然后普通的JOIN 就可以了。
select a.* from shop as a inner join shop_has_categories as b where a.shopid = b.shopid and a.cityid = 5 and b.catid = 10
and (price > 100 and price < 500)
在SHOPID、catid、price 上建立索引
select a.* from ( select * from shop where cityid = 5) as a inner join
(select * from shop_has_categories where b.catid = 10) as b
on a.shopid = b.shopid
where (price > 100 and price < 500)
select * from shop s,shop_has_categories c
where s.shopid = c.shopid and b.catid = 10 and
a.price > 100 and a.price < 500 and a.cityid = 5
在你所要查询的字段,如果那个字段所对应的表不是主键,则在该表上建索引(两个表都建)。然后直接 inner join 查询即可。
select distinct a.shopid, a.shopname, a.cityid, a.price from shop as a inner join shop_has_categories as b where a.shopid = b.shopid and a.cityid = 5 and b.catid = 10
and a.price > 100 and a.price < 500如果不是主键,需要建索引的字段:
shop 表:shopid cityid price
shop_has_categories 表:shopid catid 但是,索引建立过多就会影响插入和更新建索引字段的数据的速度。
LZ要综合考虑。