mysql - SQL优化: count all rows through subquery or own query/other improvements

Question

我正在尝试改进我的 mysql 查询。首先，我尝试优化这个简单的:

SELECT * , 
    (
        SELECT COUNT(id)
            FROM animal 
            WHERE type = :type  AND timestampadopt > 0 AND (date BETWEEN  DATE_FORMAT(CURDATE() , '%Y-%m-%d') - INTERVAL 1 YEAR AND DATE_FORMAT(CURDATE(),'%Y-%m-%d'))          
            ) AS countanimals
        FROM animal
        WHERE type = :type  AND timestampadopt > 0 AND (date BETWEEN  DATE_FORMAT(CURDATE() , '%Y-%m-%d') - INTERVAL 1 YEAR AND DATE_FORMAT(CURDATE(),'%Y-%m-%d'))
        ORDER BY timestamp DESC
        LIMIT 1, 20;

列:

id | timestampadd | timestampadopt | dateborn | animaltype | gender | chipped | smalldescger | smalldesceng | imagepath

在受影响的站点上，我循环所有动物，并进行分页。因此您可以看到 20 种动物，对于接下来的 20 种动物，您必须使用下一步按钮。

我需要知道分页时必须显示多少个站点，因此我必须计算总共有多少动物，这就是子查询的作用。

我通过分析时间来测量并得到以下结果:

0.0047s 总查询次数，
子查询0.0023s

数据库中只有5行!

在该网站上，我提供了一些过滤器，例如年龄 +/- 1 岁，以及动物是否已被领养，因此我需要在两者上使用 WHERE 子句，这可能占用最多的性能，然后是 order by 子句这是首先显示新内容所必需的。

附注我需要表中的所有列，我做了一些测试，SELECT * 具有相同的运行时间，然后像一些人建议的那样手动选择所有 10 列。

编辑:
是否值得在自己的表中排除smalltext (varchar 250)、imagpath (varchar 50)列并内部连接它们，我可能需要稍后过滤的其他列。但类型、性别、缺口都是微小的整数。

有什么改进建议吗？

我应该在主查询之外的自己的查询中执行子查询吗？

编辑:31.07

SELECT a.* , c.cnt AS countanimals
        FROM animal  a
        JOIN (
            Select a1.date AS date1, a1.tmstmpadopt AS tmstmpadopt1, a1.type AS type1, COUNT(a1.id) as cnt 
            FROM animal a1
            GROUP BY date1, tmstmpadopt1, type1
            ) c on (a.date = c.date1 AND a.tmstmpadopt = c.tmstmpadopt1 AND a.type = c.type1)

        WHERE a.type = 1 AND tmstmpadopt = 0 AND (date BETWEEN  DATE_FORMAT(CURDATE() , '%Y-%m-%d') - INTERVAL 100 YEAR AND DATE_FORMAT(CURDATE(),'%Y-%m-%d')- INTERVAL 1 YEAR)
        ORDER BY a.timestamp DESC
        LIMIT 1, 20;

Answer 1

内联 View 可能会对您有所帮助。所以试试这个

SELECT a.*,c.cnt AS countanimals
FROM animal a 
       join (Select a1.dateborn, a1.timestampadopt, count(a1.id) as cnt 
             from animals a1
             Where a1.timestampadopt > 0
               and a1.type = :type
             group by a1.dateborn, a1.timestampadopt) c on (a.dateborn = c.dateborn and a.timestampadopt = c.timestampadopt)
WHERE a.type = :type
AND a.timestampadopt > 0 
AND a.dateborn BETWEEN  DATE_FORMAT(CURDATE(),'%Y-%m-%d')-INTERVAL 1 YEAR AND DATE_FORMAT(CURDATE(),'%Y-%m-%d'))
ORDER BY a.timestamp DESC
LIMIT 1, 20;