EXPLAIN SELECT
s.id,
s.name,
s.price,
s.unit,
s.qty,
s.remark,
s.image,
s.cuft,
s.volume,
s.status,
s.vender_id AS venderId,
s.booth_id AS boothId,
s.booth_no AS boothNo,
s.show_booth_no AS showBoothNo,
s.name_en AS nameEn,
s.bar_code AS barCode,
s.product_code AS productCode,
s.type_one_id AS typeOneId,
s.type_one_name AS typeOneName,
s.type_two_id AS typeTwoId,
s.type_two_name AS typeTwoName,
s.ep_price AS epPrice,
s.pack_cn AS packCn,
s.pack_en AS packEn,
s.inner_box AS innerBox,
s.out_chest_long AS outChestLong,
s.out_chest_width AS outChestWidth,
s.out_chest_height AS outChestHeight,
s.pack_long AS packLong,
s.pack_width AS packWidth,
s.pack_height AS packHeight,
s.sample_long AS sampleLong,
s.sample_width AS sampleWidth,
s.sample_height AS sampleHeight,
s.can_supply AS canSupply,
s.rough_weight AS roughWeight,
s.net_weight AS netWeight,
s.is_delete AS isDelete,
s.create_by AS createBy,
s.create_date AS createDate,
s.update_by AS updateBy,
s.update_date AS updateDate,
s.credentials AS credentials,
s.credentials_id AS credentialsId,
s.vender_name AS venderName,
s.vender_code AS venderCode,
s.booth_no AS boothNo,
(SELECT
sl.name
FROM
sys_login sl
WHERE id = s.update_by) AS updateName,
(SELECT
sl.name
FROM
sys_login sl
WHERE id = s.create_by) AS createName,
v.contact1 AS contact1,
v.tel1 AS tel1,
v.phone1 AS phone1,
v.meet_phone AS meetPhone,
v.fax AS fax,
v.qq AS qq,
v.sms_code AS smsCode,
(SELECT
bh.end_date
FROM
booth_hire bh
WHERE bh.booth_no = s.booth_no
AND bh.vender_id = s.vender_id
AND bh.status = 0
LIMIT 1) AS boothEndDate,
(SELECT
sl.name
FROM
sys_login sl
WHERE sl.id = s.create_by) AS createName,
(SELECT
sl.name
FROM
sys_login sl
WHERE sl.id = s.update_by) AS updateName,
s.sold_out_by AS soldOutBy,
s.sold_out_date AS soldOutDate,
s.sold_out_type AS soldOutType
FROM
sample s
LEFT JOIN vender v
ON s.vender_id = v.id
WHERE 1 = 1
AND s.status != 3
AND s.status != - 1
AND s.is_delete = 0
group by s.vender_id
LIMIT 0,100
3 回答
慕勒3428872
TA贡献1848条经验 获得超6个赞
首先，select里面写join看着超级烦，为什么不能放在from里面join呢，怎么看怎么不顺眼，还有createName、updateName要写两遍？
然后，！=是不会走索引的，还有is_delete这种性别字段一样属性，如果过滤结果集基本都是很少的情况下可以用一下索引，否则加了索引本就没多少意义。这时候有个group by字段选择性更好的话，当然就会用group的索引了，因为反正你都要回表查询的。
So。索引失效很正常，如果一定要用的话，加force index好了
慕田峪7331174
TA贡献1828条经验 获得超13个赞
测试表qxd_user中number是索引列，reg_dev是普通列，没有加索引
1.group by 没加条件的时候是全表扫描
2.请用where条件带上索引,先后关系是先where再group by
3.group by 索引列，这样可以走索引
- 3 回答
- 0 关注
- 2878 浏览
添加回答
举报
0/150
提交
取消