订单表 27个字段 3500条记录
地址表11个字段 3500条记录
员工表 14个字段 200条记录
现在多表查询500条订单
sql语句
SELECT
o.orderid,
o.consignee,
o.mobile,
o.type,
o.state,
o.ordercode,
o.fee,
o.orderno,
o.sourcetype,
o.releasetime,
o.accepttime,
o.courierid,
c.couriername,
c.mobile AS couriermobile,
t.address AS target_address,
t.info AS target_info
FROM mi_orders o
LEFT JOIN mi_couriers c ON o.courierid = c.courierid
LEFT JOIN mi_targetaddress t ON o.orderid = t.orderid
WHERE (o.state='5' OR o.state='4' OR o.state='3' OR o.state='2') AND (o.type='1' OR o.type='2')
ORDER BY o.state ASC,o.accepttime DESC,o.orderno DESC
LIMIT 0,500
我查500条记录3.5s
我只查十条记录也需要3.1s
应该是查询优化方面的问题,求大神解答,谢谢!
地址表11个字段 3500条记录
员工表 14个字段 200条记录
现在多表查询500条订单
sql语句
SELECT
o.orderid,
o.consignee,
o.mobile,
o.type,
o.state,
o.ordercode,
o.fee,
o.orderno,
o.sourcetype,
o.releasetime,
o.accepttime,
o.courierid,
c.couriername,
c.mobile AS couriermobile,
t.address AS target_address,
t.info AS target_info
FROM mi_orders o
LEFT JOIN mi_couriers c ON o.courierid = c.courierid
LEFT JOIN mi_targetaddress t ON o.orderid = t.orderid
WHERE (o.state='5' OR o.state='4' OR o.state='3' OR o.state='2') AND (o.type='1' OR o.type='2')
ORDER BY o.state ASC,o.accepttime DESC,o.orderno DESC
LIMIT 0,500
我查500条记录3.5s
我只查十条记录也需要3.1s
应该是查询优化方面的问题,求大神解答,谢谢!
WHERE o.state in ('2','3','4','5')试试
explain select ....
及
show index from 以供分析
另外描述一下你SQL语句想实现的查询功能,这样可以从更深层次上的逻辑上优化。