似乎只有count子查询中才需要时间范围。
为什么不把where语句移到子查询中呢？这将大大减少运行时间：

SELECT DISTINCT(cp.payment_ref) as ref,
 (SELECT COUNT(id) FROM client_payments 
  WHERE status LIKE 'Declined' 
  AND payment_ref = ref
  AND payment_date BETWEEN '2018-05-14 00:00:00' AND '2018-05-14 23:59:59') as declined 
 FROM client_payments as cp

谢谢你的回复。最快运行的查询如下.08秒 SELECT cp.payment_ref as ref,count(status='Declined') as no_declined FROM client_payments as cp WHERE cp.payment_date BETWEEN '2018-05-14 00:00:00' AND '2018-05-14 23:59:59' GROUP BY cp.payment_ref HAVING COUNT(status='Declined')>2

我发现不需要依赖子查询来获取计数，您可以从主查询中获取它，并为所需的计数去掉distinct use聚合

SELECT cp.payment_ref, COUNT(cp.id) cnt
FROM client_payments AS cp 
WHERE cp.payment_date BETWEEN '2018-05-14 00:00:00' AND '2018-05-14 23:59:59'
AND cp.`status` = 'Declined'
GROUP BY cp.payment_ref
HAVING cnt >= 3

还要检查是否使用了查询和搜索键/索引的解释计划

在确定你有索引之后 payment_ref , timestamp 以及 status ，我会用自连接来解决它：

SELECT @starttimestamp:='2018-05-14 00:00:00';
SELECT @endtimestamp:='2018-05-14 23:59:59';

SELECT
  first.payment_ref,
  first.timestamp, first.name, first.status,
  second.timestamp, second.name, second.status,
  third.timestamp, third.name, third.status
FROM
  transactions AS first

  -- find a later declined transaction
  INNER JOIN transactions AS second
    ON first.payment_ref=second.payment_ref
    AND first.timestamp<second.timestamp
    AND second.timestamp<=@endtimestamp
    AND second.status LIKE 'Declined%'

  -- find an even later declined transaction
  INNER JOIN transactions AS third
    ON second.payment_ref=third.payment_ref
    AND second.timestamp<third.timestamp
    AND third.timestamp<=@endtimestamp
    AND third.status LIKE 'Declined%'

WHERE first.timestamp BETWEEN @starttimestamp AND @endtimestamp
AND first.status LIKE 'Declined%'
;

这使得索引得到最佳利用，并且通过关键范围扫描具有非常高的选择性
如果您真的需要逐行表单，可以通过只运行一次的 Package 器查询对其进行转换。