如何与工会相结合？mysql

Question

我有两张桌子：

1. 历史
2. 业务

我想运行以下查询：

SELECT name, talias.*
FROM
    (SELECT business.bussName as name history.*
        FROM history 
        INNER JOIN business on history.bussID = business.bussID
        WHERE history.activity = 'Insert' OR history.activity = 'Update' 
    UNION 
        SELECT name as Null, history.*
        FROM history 
        WHERE history.activity = 'Delete'

    ) as talias  
WHERE 1 
order by talias.date DESC
LIMIT $fetch,20

这个查询耗时13秒，我认为问题是Mysql连接历史记录和业务表中的所有行！而它应该只加入20行！

我怎么才能解决这个问题？

Answer 1

试试这个：

SELECT h.*
FROM history AS h
WHERE (h.activity IN ('Insert', 'Update')
        AND EXISTS (SELECT * FROM business AS b WHERE b.bussID = h.bussID))
    OR h.activity = 'Delete'
ORDER BY h.date DESC
LIMIT $fetch, 20

要使ORDER BY和LIMIT高效，请确保在history.date上有索引。