On Mon, Sep 29, 2008 at 10:29:45AM +0200, kiki@xxxxxxx wrote: > >> EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE id_camera='3' AND > >> confirmed='false' AND dismissed='false' ORDER BY date DESC, time DESC > >> LIMIT 1; > Sorry, without LIMIT returns around 700000 rows. > Tried to index date column and time column but the performance is pretty > much the same. > Everything is OK, I just don’t understand way is this query burdening the > processor so much. 1. please do not top-post. 2. for this query, you can use this index: create index xxx on system_alarm (id_camera, date, time) where confirmed = 'false' and dismissed = 'false'; or you can make it without where: create index xxx on system_alarm (id_camera, confirmed, dismissed, date, time); but if you usually have the criteria "confirmed = 'false' and dismissed = 'false'" then the first index should be faster. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@xxxxxxxxxx / aim:depeszhdl / skype:depesz_hdl / gg:6749007
