Help needed streamlining a WHERE condition

All I need are variables!

[alexdunae]

Hi SQL gurus,

I've got a condition in a SQL query that seems incredibly inefficient.

Basically, if the month (1-12) of `event_begin` is bigger than the month of `event_end` (e.g. spans December to January) then subtract 12 from the beginning month and add 12 to the end month to allow them to be compared.

Here's the extract for January

SELECT *  
 FROM table  
 WHERE 
 1 >= IF(EXTRACT(MONTH FROM event_begin) > EXTRACT(MONTH FROM event_end), EXTRACT(MONTH FROM event_begin)-12, EXTRACT(MONTH FROM event_begin)) 
 AND 
 1 <= IF(EXTRACT(MONTH FROM event_begin) > EXTRACT(MONTH FROM event_end), EXTRACT(MONTH FROM event_end)+12, EXTRACT(MONTH FROM event_end)) 

There must be a better way. Failing that, does anybody now if the results of

EXTRACT(MONTH FROM event_begin)

will be stored in memory during the query or will it be calculated each time?

Thanks in advance,

Alex

[phranque]

not sure exactly what your where clauses are doing but perhaps the PERIOD_DIFF function would be more useful and/or efficient...

[SeanW]

Read up on the EXPLAIN command, it will tell you exactly how the planner would execute your query, which can help you make it more efficient.

Sean