[RESOLVED] Trying to correct query

rprincejr

I currently have the following query;

SELECT a.schedID,
	a.start AS eventDate, b.div_id AS divisionID, b.div_name AS divisionName
	FROM schedules a
	INNER JOIN divisions b ON b.div_id = a.div_id
	WHERE date_format(a.start, '%Y-%m-%d') >= '2010-01-01'
	AND DATE_ADD(a.start, INTERVAL 5 DAY) <= CURDATE()
	AND NOT EXISTS (SELECT results_id FROM results e WHERE e.schedID = a.schedID)
	ORDER BY eventDate ASC;

Im trying to basically find any schedules that do not have any results 5 after the schedule date. My current query has major performance issues. It also times out inconsistently. Is there a different way to write the query? Im at a mental roadblock. Any help is appreciated.

johanafm

First off, use EXPLAIN in front of the query to see what the query execution plan looks like.

Make sure that all relevant fields are indexed. That is, anything used in the ON clause of a join and anything found in the WHERE clause. In case you are using compound indexes, do note that and index of (field1, field2) is usable for field1 alone, field1 and field 2 together, but not for field2 alone. You can see what indices exist for a table by issuing SHOW CREATE TABLE tablename.

I suspect you are using a DBMS which allready handles dates according to the SQL standard, Y-m-d, which means that it's pointless to explicitly format your dates the way they are allready formatted.

I do not know how smart the handling of things like this is

DATE_ADD(a.start, INTERVAL 5 DAY) <= CURDATE()

but if it's not optimized internally, you can do it manually instead. First up, get the date of 4 days ago, before you execute the query, either by

SELECT @before_date = DATE_SUB(CURDATE(), INTERVAL 5 DAY);

or do it in PHP or whatever language you are using, and supply this value directly into the query. Then

a.start < @before_date

Once you have appropriate indexing and have modified the above stuff, try executing the query again and see how it does. If you still have problems, post the results of your EXPLAIN.

rprincejr

Johana, those are excellent tips. I did double check my indices and I found I needed to add one indice. Im not sure it helped that much in performance though. I ended up changing my query to fix the problem.

Using NOT EXISTS in a query is very expensive in terms of performance. So I switched the query around and LEFT JOIN'ed the results and added a 'is NULL' check in the WHERE statement. This solved the problem.

Here is the revised query. Hope it helps someone down the road.

SELECT a.sched_id,
	a.start AS eventDate,
	b.div_id AS divisionID,
	b.div_name AS divisionName
	FROM schedules a
	INNER JOIN divisions b ON b.div_id = a.div_id
	LEFT JOIN results e ON a.sched_id = e.sched_id
	WHERE DATE_ADD(a.start, INTERVAL 5 DAY) <= CURDATE() AND e.sched_id is NULL
	ORDER BY eventDate ASC;