Want to take out the goods in the table don't repeat the cas goods_id data of the corresponding

Goods_id is a primary key cas and is_check and is_sale built a regular index
Select goods_id from hxj_goods where is_check=1 and is_sale=1 group by cas limit 51992, 8 - performed 29 seconds,

Select goods_id from hxj_goods where is_check=1 and is_sale=1 group by cas limit 0, 8 - carried out 0.002 seconds,

Select goods_id from (select goods_id, cas from hxj_goods WHERE is_check=1 and is_sale=1) a group by cas limit 0, 8 - performed 16 seconds, if the limit is bigger more slowly, than the one in front of the poor,

The select goods_id from hxj_goods where is_check=1 and limit is_sale=1, 8-235472 performed after removing group by 0.7 seconds, but the result is not wanted,

Select distinct (cas), goods_id from hxj_goods where is_check=1 and is_sale=1 limit 312, 8-0.001 seconds performed using distinct, but his results are right no one page data to heavy, not all data to heavy paging,

Select goods_id from (select distinct (cas), goods_id from hxj_goods where is_check=1 and is_sale=1) a limit of 16, 8 - performed 8 seconds slow,

CodePudding user response:

The speed and actually group by cas and limit,

The second is fast, relative to the first, is group by generating the eight data, returned directly, and the first, to generate at least 51992 + 8 data, to return data,

CodePudding user response:

The subquery are improving in version 5, 7, two-thirds of the gap should not be that big
Other information as to the performance difference, in fact already very clear, you yourself wrote does not involve group by, or in front of the group by paging, small performance difference, of course, if the group by then paging, certainly after the page, the more data need to group by, the more powerful performance to download

CodePudding user response:

Two-thirds of you may have a look execution plan, in version 5.7, the two execution plans should be the same
(seen before this edition is a problem, similar to the query two-thirds, account main execution plan of 5.6, the subquery is two steps, in 5.7, is a step)