求大佬告知union分页如何优化?
SELECT title,
img_url,
img_border,
url,
ext,
product_id,
product_type,
up_time,
min_price,
max_price
FROM (
(SELECT title,
re_group_content.img_url,
img_border,
url,
re_group_content.ext,
product_id,
product_type,
now() AS up_time,
0 AS min_price,
0 AS max_price
FROM re_group_content
LEFT JOIN re_product ON re_group_content.product_id = re_product.id
AND re_group_content.product_type = 'common_product'
LEFT JOIN re_blind_box ON re_group_content.product_id = re_blind_box.id
AND re_group_content.product_type = 'blind_box'
WHERE re_group_content.status = 1
AND group_id = 12
AND ((re_product.status = 1
AND re_product.up_time <= now())
OR re_blind_box.status = 1) )
UNION
(SELECT '' AS title,
img_url,
'' AS img_border,
'' AS url,
'' AS ext,
j.id AS product_id,
'common_product' AS product_type,
j.up_time,
IFNULL(min(sale_price), 0) AS min_price,
IFNULL(min(prime_price), 0) AS max_price
FROM re_product j
LEFT JOIN re_product_package k ON j.id = k.product_id
AND k.package_type != 'url'
WHERE j.status = 1
AND up_time <= now()
GROUP BY j.id)
UNION
(SELECT '' AS title,
img_url,
'' AS img_border,
'' AS url,
'' AS ext,
id AS product_id,
'blind_box' AS product_type,
created_at AS up_time,
sale_price AS min_price,
sale_price AS max_price
FROM re_blind_box
WHERE status = 1
AND sale_price != 0 )) m
ORDER BY up_time DESC
LIMIT 0, 10
上面SQL不考虑索引优化,MYSQL函数优化的问题哈。只针对UNION,对于多union分页例子,有什么优化的思路吗?
另外,使用 union 的话,表的数据大概多少以内用union做分页好点?
推荐文章: