On Thu, Dec 22, 2005 at 09:47:11AM -0600, Jim C. Nasby wrote: > On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote: > > SELECT id > > FROM mq > > WHERE now - start_date > time_to_live; > > The problem is you can't use an index on this, because you'd need to > index on (now() - start_date), which obviously wouldn't work. Instead, > re-write the WHERE as: > > WHERE start_date < now() - time_to_live
Unless I'm missing something that wouldn't use an index either, because the planner wouldn't know what value to compare start_date against without hitting each row to find that row's time_to_live. But something like this should be able to use an expression index on (start_date + time_to_live): WHERE start_date + time_to_live < now() -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
