商品表和商品的图片表是分开的,商品表里没有为图片加个冗余字段。
现在对商品的各种操作中都要取商品对应图片表中顺序值最小的图片.select * from (
select goods.id, goods.name, picture from goods
left join goods_pic on goods.id = goods_pic.goods_id
order by show_order asc
) group by id;也试过另一种方案:select goods.id, goods.name, (select picture from goods_pic where goods.id = goods_pic.goods_id order by show_order limit 1) from goods ;但是这两种方案都不太理想。不知道大家有没有高招。
现在对商品的各种操作中都要取商品对应图片表中顺序值最小的图片.select * from (
select goods.id, goods.name, picture from goods
left join goods_pic on goods.id = goods_pic.goods_id
order by show_order asc
) group by id;也试过另一种方案:select goods.id, goods.name, (select picture from goods_pic where goods.id = goods_pic.goods_id order by show_order limit 1) from goods ;但是这两种方案都不太理想。不知道大家有没有高招。
有索引的。执行时间是0.138sec的
我想把这个时间再降一个级别。
'2', 'DERIVED', 'goods', 'ref', 'i_goods_category1_id', 'i_goods_category1_id', '4', '', '5687', 'Using where; Using temporary; Using filesort'
'2', 'DERIVED', 'goods_pic', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'goods.id', '1', ''
create index xx on goods_pic (goods_id,show_order);
select goods.id, goods.name, (select picture from goods_pic where goods.id = goods_pic.goods_id order by show_order limit 1) from goods ;