[MySQL help] helps a friend optimize the SQL process-) from 4S to 0.011 seconds _ MySQL

[MySQL help] helps a friend optimize the SQL process-) from 4S to 0.011 seconds bitsCN.com this SQL runs slowly, 4 seconds

SELECT (op.cash_payment+op.pos_payment+op.online_payment+op.charge_card_payment) AS real_payment,op.* FROM db_order.`order_payment` AS op WHERE op.order_payment_id in (SELECT oi.order_payment_id FROM db_order.`order_info` AS oi WHERE oi.batch_order_id = '123456') ORDER BY op.insert_time DESC

I asked a friend to post the explain result, as shown in:
I can see that there is an in behind the where clause, which is probably caused by this in, but there may be a problem with the data volume, so I will confirm the data volume in the table below again to get the information. Order_info 44588
Order_payment 74601The data volume is small, so it is estimated that the large data volume may be the in problem. So change the SQL statement as follows:

SELECT   (op.cash_payment+op.pos_payment+op.online_payment+op.charge_card_payment) AS real_payment,op.*   FROM db_order.`order_payment` AS op,db_order.`order_info` oi WHERE op.order_payment_id=oi.order_payment_id and oi.batch_order_id = '123456'  ORDER BY op.insert_time DESC

Sent to a friend, who said it was quite fast in 0.011 S.

It seems that this IN must be used with caution, especially when two tables are associated, replace the in with the inner link.

BitsCN.com