当sql有子查询时count比分页查数sql还慢

biz_order表数据量 80万条

biz_user_box数据量 89条

分页查数据

SELECT * FROM biz_order WHERE STATUS IN ('PAID') AND create_time >= '2024-01-17 00:00:00' AND create_time <= '2024-01-18 23:55:59' AND box_id IN (SELECT box_id FROM biz_user_box WHERE user_id = '65909bf4abe8440cae422e5aa6e7f13c')

order by create_time desc limit 0,10,

查询总数

SELECT count(1) FROM biz_order WHERE STATUS IN ('PAID') AND create_time >= '2024-01-17 00:00:00' AND create_time <= '2024-01-18 23:55:59' AND box_id IN (SELECT box_id FROM biz_user_box WHERE user_id = '65909bf4abe8440cae422e5aa6e7f13c')

尝试

改用inner jion

SELECT COUNT(1)

FROM biz_order

INNER JOIN biz_user_box ON biz_order.box_id = biz_user_box.box_id

WHERE biz_order.STATUS = 'PAID'

AND biz_order.create_time BETWEEN '2024-01-17 00:00:00' AND '2024-01-18 23:55:59'

AND biz_user_box.user_id = '65909bf4abe8440cae422e5aa6e7f13c'

依旧耗时5秒，没有改善

优化

由于biz_user_box数据量少，不用子查询，直接in数据

SELECT count(1) FROM biz_order WHERE STATUS IN ('PAID') AND create_time >= '2024-01-17 00:00:00' AND create_time <= '2024-01-18 23:55:59' AND box_id IN ('3d283b952e584ea9b170318709c314f0','40f38a3e82f6465aaf7aae00779534b7''80afb2325aa944239b6749488fb27e7c','24e58ef5b7294d618a263dca7bbcdfe7','39433ffa2c3d4bb8b502b393d61b94b6','9a0ee8898344413da110e9e366e0247f','b55a4ccd51bf4295a34a750d926d2214','f85a1729de0f41c09d1a0b89556e7c34','16d8e14239944ddda77adf4ba8689e67','1157214440525946882','f443efb093d14646a252e345281bf9de','d858a406c3e446be82b496372d3e1dcf','c556045e9c5d40ff8b1d580c6d102b91','b741ad0578724ec6bfec51753fa5da3d','1159136876456599554','1157272503868903425','c5a9fd9c07364cf3b8e30c129a9f4e5e','b8afb1f46a7240e380fe81dd82b17012')