我有一个包含7个表的Mysql数据库。我的问题是数据库中有数百万条记录，以下查询需要大量时间。我该怎么办？
(SELECT 'res' AS TBL
, mlsnum
, streetname
, streetnum
, listdate
, gpext_latitude
, gpext_longitude
, zipcode
, listprice
, beds
, bathsfull
, sqftbldg
, modified
, yearbuilt
FROM rets_property_res
WHERE liststatus = "active")
UNION
ALL
(SELECT 'rnt' AS TBL
, mlsnum
, streetname
, streetnum
, listdate
, gpext_latitude
, gpext_longitude
, zipcode
, listprice
, beds
, bathsfull
, sqftbldg
, modified
, yearbuilt
FROM rets_property_rnt
WHERE liststatus = "active")
UNION
ALL
(SELECT 'lnd' AS TBL
, mlsnum
, streetname
, streetnum
, listdate
, gpext_latitude
, gpext_longitude
, zipcode
, listprice
, NULL AS BEDS
, NULL AS BATHSFULL
, NULL AS SQFTBLDG
, modified
, NULL AS YEARBUILT
FROM rets_property_lnd
WHERE liststatus = "active")
UNION
ALL
(SELECT 'hir' AS TBL
, mlsnum
, streetname
, streetnum
, listdate
, gpext_latitude
, gpext_longitude
, zipcode
, listprice
, beds
, bathsfull
, sqftbldg
, modified
, yearbuilt
FROM rets_property_hir
WHERE liststatus = "active")
UNION
ALL
(SELECT 'cnd' AS TBL
, mlsnum
, streetname
, streetnum
, listdate
, gpext_latitude
, gpext_longitude
, zipcode
, listprice
, beds
, bathsfull
, sqftbldg
, modified
, yearbuilt
FROM rets_property_cnd
WHERE liststatus = "active")
UNION
ALL
(SELECT 'mul' AS TBL
, mlsnum
, streetname
, streetnum
, listdate
, gpext_latitude
, gpext_longitude
, zipcode
, listprice
, NULL AS BEDS
, NULL AS BATHSFULL
, sqftbldg
, modified
, yearbuilt
FROM rets_property_mul
WHERE liststatus = "active")
ORDER
BY listdate DESC
LIMIT 0, 48
所有查询都是不同的。请尝试更换UNION用UNION ALL。不必删除重复项应该可以节省很多。
为了节省更多，然后将每个子查询限制为48行，并将它们的短语设置为：
SELECT 'res' AS TBL, . . .
FROM rets_property_res
WHERE liststatus = 'active'
ORDER BY listdate DESC
LIMIT 48
在和的每个表上都有一个索引(liststatus, listdate)，查询可能会变得非常快。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句