This query consistently takes about 30 seconds to execute. Does anything look out of the ordinary? I have three separate indexes for the notifications table. One on the memberID column, notificationID column, and priority column. For the members table, among others, I have an index made on the memberID column.
Thanks!
SELECT
m.firstName,
m.lastName,
m.bouncing,
n.notificationID,
n.memberID,
date_format(n.dateAdded, '%m-%d-%Y') as dateAddedFormatted,
n.type,
n.priority,
n.errorMessage,
date_format(n.dateScheduled, '%m-%d-%Y') as dateScheduledFormatted,
attempts,
date_format(n.lastAttempt, '%m-%d-%Y') as lastAttemptFormatted
FROM
notifications n
JOIN
members m
ON
n.memberID = m.memberID
WHERE
n.dateSent is null
and n.dateScheduled <= current_date()
and n.lastAttempt is not null
ORDER BY
n.dateAdded desc;
My query has changed slightly since I originally posted, no longer using a join, but I still have the same problem. Sometimes it executes lightning quick, other times it takes so long that I eventually get a server 500 error due to timing out. Here is the query:
SELECT
priority,
type,
count( case when dateSent is not null then 1 else null end ) as 'sent',
count( case when dateSent is null then 1 else null end ) as 'pending',
count( case when error is not null then 1 else null end ) as 'errors',
count( case when date(dateScheduled) > current_date() then 1 else null end ) as 'future'
FROM
notifications
WHERE
(date(dateScheduled) = current_date() or date(dateSent) = current_date() or date(lastAttempt) = current_date())
GROUP BY
type
ORDER BY
priority, type;
When I do an EXPLAIN on it, this is what is reported:
id = 1
select_type = SIMPLE
table = notifications
type = ALL
possible_keys = null
key = null
key_len = null
ref = null
rows = 287,657
extra = Using where; Using temporary; Using file sort
If the slowness was due to not creating the right index, then the query should be slow each and every time it’s run, right?
[quote=“busboy, post:3, topic:223959, full:true”]If the slowness was due to not creating the right index, then the query should be slow each and every time it’s run, right?
[/quote]
that would be my guess as well
hey, what would you say to making your WHERE clause sargable?