Case study of table join query not equal to Optimization

The original SQL statement is as follows:

SELECT            o.order_id            FROM orders o            JOIN shipping_orders so ON so.order_id = o.order_id            JOIN shippings s ON s.shipping_no = so.shipping_no            WHERE o.payment_method = 'COD'AND o.status IN(3, 4, 6, 11, 12)AND s.logistic_track_no <> ''AND o.delivery_time < unix_timestamp('2013-09-01')AND o.order_id NOT IN (SELECT order_id FROM cod_order_extinfos);

shipping_ordersTable andshippingsTable Association to retrieve records not equal to null, because s. logistic_track_no <>''If the index is not used, it will scan the entire table. Therefore, the purpose of optimization is to retrieve the index first.shippingsIf the table is empty, the result set is very small and the join operation is much faster.

Rewrite as follows:

SELECT            o.order_id            FROM orders o            JOIN shipping_orders so ON so.order_id = o.order_id            LEFT JOIN (select shipping_no,logistic_track_no from shippings where logistic_track_no = '') s            ON s.shipping_no = so.shipping_no            WHERE o.payment_method = 'COD'AND o.status IN(3, 4, 6, 11, 12)AND s.logistic_track_no is nullAND o.delivery_time < unix_timestamp('2010-12-31')AND  NOT EXISTS ( SELECT order_id FROM cod_order_extinfos coe where coe.order_id=o.order_id);

Execution time before optimization: 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/2059341318-0.jpg "title =" optimization before .jpg "alt =" 161750987.jpg"/>

Execution time after optimization:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/2059346495-1.jpg "title =" after optimization .jpg "alt =" 161828823.jpg"/>

This article is from the "hechun's technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1300162