MySQL query performance degrades over time
I have a rather complex query that runs just fine (about 1 second) when the server is freshly started. But after half a day or so, the query slows to a crawl (about 25 seconds). Other queries are not affected and run as fast as always. I have to restart the server to get the original performance back. In the course of a day, the query is performed less than 20 times. It doesn't appear to degrade slowly, it's abrupt. I'll be testing it for hours and it's fine, then I come back after lunch and it's slowed down to 25 seconds.
The database has 4 tables, and I'm only querying 2 of them. One table has 25 records, and the other has several hundred thousand.
I'm not sure where to even begin with this. Shouldn't the same query take the same (roughly) amount of time each time it's performed, assuming the server is not maxing out on some other process (it's not - it's sitting quietly idle)?
Here's the query. I'm happy to provide more info, server config, stats, etc. Thanks.
-Bill
select
rn.*,
count(*) as ct,
sum(rn.status) as sm,
max(rn.min_nights) as max_mnights,
r.name,
r.building,
r.desc_long,
ra1.status as status_1,
ra1.night_date as date1,
ra2.status as status_2,
ra2.night_date as date2
from room_nights rn, rooms r, room_nights ra1, room_nights ra2
where rn.night_date between '2008-09-24' and '2008-09-25' - interval 1 day
and rn.room_code = r.code
and r.capacity >= 1
and (r.code = ra1.room_code and '2008-09-23' = ra1.night_date)
and (r.code = ra2.room_code and '2008-09-25' = ra2.night_date)
group by rn.room_code
having ct=sm and
(
max_mnights <= 1 or
(
max_mnights = 3 and
(
(dayofweek('2008-09-25') in (7,1) and status_2 > 1) or
(dayofweek('2008-09-24') in (1,2) and status_1 > 1)
) or
max_mnights = 2 and
(
(dayofweek('2008-09-25') in (7) and status_2 > 1) or
(dayofweek('2008-09-24') in (1) and status_1 > 1)
)
)
)
order by rn.rate desc