8000种商品情况下
SELECT
m.*,
u.NAME unitName,
mc.NAME categoryName,
me.bar_code,
me.purchase_decimal,
me.commodity_decimal,
me.wholesale_decimal,
me.low_decimal,
me.sku
FROM
jsh_material m
LEFT JOIN jsh_material_extend me ON me.tenant_id = 63
AND m.id = me.material_id
AND ifnull( me.delete_Flag, '0' ) != '1'
LEFT JOIN jsh_unit u ON u.tenant_id = 63
AND m.unit_id = u.id
AND ifnull( u.delete_Flag, '0' ) != '1'
LEFT JOIN jsh_material_category mc ON mc.tenant_id = 63
AND m.category_id = mc.id
AND ifnull( mc.delete_Flag, '0' ) != '1'
WHERE
m.tenant_id = 63
AND 1 = 1
AND ifnull( m.delete_flag, '0' ) != '1'
GROUP BY
m.id
ORDER BY
m.id DESC
LIMIT 0,
10
未优化前需要20s左右。
jsh_material_extend增加对material_id字段的索引，查询速度一秒内