What is the best way to combine these two queries into one?
select count(*) as scheduled from notifications where date_format(dateScheduled, '%Y-%m-%d') = curdate();
select count(*) as delivered from notifications where date_format(dateScheduled, '%Y-%m-%d') = curdate() and dateSent is not null;
select
(select count(*) from notifications where date_format(dateScheduled, '%Y-%m-%d') = curdate()) as scheduled,
(select count(*) from notifications where date_format(dateScheduled, '%Y-%m-%d') = curdate() and dateSent is not null) as delivered
FROM notifications group by scheduled, delivered;
while that does work, itβs not the most efficient approach
a simpler method is this β
WHERE DATE(dateScheduled) = CURRENT_DATE()
however, this too suffers from an efficiency problem
using a function on a table column pretty much rules out index optimization, and the database optimizer will often just do a table scan
here is a much better approach that will utilize an index on the column β
WHERE dateScheduled >= CURRENT_DATE()
AND dateScheduled < CURRENT_DATE() + INTERVAL 1 DAY
you also used two complete passes of the data, whereas you really only need one β
SELECT COUNT(*) AS scheduled
, COUNT(dateSent) AS delivered
FROM notifications
WHERE dateScheduled >= CURRENT_DATE()
AND dateScheduled < CURRENT_DATE() + INTERVAL 1 DAY