求mysql查询语句优化

zhuhao19851021 2016-12-17 02:32:11



SELECT  `order_id`,`delivery_date`,`buyer_name`,`order_status`,`ship_service_level`,

`payments_date` ,`account` ,`delivery_warehouse` 

FROM `Sale_Order`

WHERE (  `account`='EU_AC'  OR   `account`='JP_AC'  OR   `account`='JP_John'  OR   `account`='JP_CAO'  OR   `account`='JP_Fheimin'  OR   `account`='US_CAO'   OR   `account`='JP_07'  OR   `account`='LAZADA'  OR   `account`='qoo10'  OR   `account`='JP09' )

 AND ( `order_id` LIKE '%411506-240700%' OR  `item_list` LIKE '%411506-240700%' OR  `sku_list` LIKE '%411506-240700%' OR  `asin_list` LIKE '%411506-240700%' OR  `recipient_name` LIKE '%411506-240700%' OR  `country` LIKE '%411506-240700%' OR `delivery_number` LIKE '%411506-240700%' OR `buyer_email` LIKE '%411506-240700%'  OR `logistics_number` LIKE '%411506-240700%'  OR `ship_address` LIKE '%411506-240700%' OR `ship_state` LIKE '%411506-240700%' OR `city` LIKE '%411506-240700%'  OR `phone` LIKE '%411506-240700%' )

limit  0,10

现在查询一次要2-3秒，页面显示结果需要8-10秒
请问怎么优化这个查询语句，索引都加了，数据才16万多而以

...全文

185 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

傲雪星枫 2016-12-18

打赏
举报

可以使用explain分析，是否命中索引，遍历行数等。


explain SELECT  `order_id`,`delivery_date`,`buyer_name`,`order_status`,`ship_service_level`,
`payments_date` ,`account` ,`delivery_warehouse` 
FROM `Sale_Order`
WHERE (  `account`='EU_AC'  OR   `account`='JP_AC'  OR   `account`='JP_John'  OR   `account`='JP_CAO'  OR   `account`='JP_Fheimin'  OR   `account`='US_CAO'   OR   `account`='JP_07'  OR   `account`='LAZADA'  OR   `account`='qoo10'  OR   `account`='JP09' )
 AND ( `order_id` LIKE '%411506-240700%' OR  `item_list` LIKE '%411506-240700%' OR  `sku_list` LIKE '%411506-240700%' OR  `asin_list` LIKE '%411506-240700%' OR  `recipient_name` LIKE '%411506-240700%' OR  `country` LIKE '%411506-240700%' OR `delivery_number` LIKE '%411506-240700%' OR `buyer_email` LIKE '%411506-240700%'  OR `logistics_number` LIKE '%411506-240700%'  OR `ship_address` LIKE '%411506-240700%' OR `ship_state` LIKE '%411506-240700%' OR `city` LIKE '%411506-240700%'  OR `phone` LIKE '%411506-240700%' )
limit  0,10;