Very slow join query

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;

what does the EXPLAIN say? how are your WHERE conditions affecting the execution plan?

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?

Thank you!

[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?

does each type have only one priority?

because if not, your grouping is farked

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.