Re: CPU load

On Mon, Sep 29, 2008 at 10:29:45AM +0200, kiki@fesb.hr 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@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007