The item table is like this
id category_id name registered
I have to show total number of items from the beginning. Suppose between 2013-05-01 to 20-05--05, I have to count all the items before 2013-05-01 and add that to count of 2013-05-01 's items. The result have to be grouped by registered and category_id. I have to do this in one query. This is my query
SELECT registered, me.category_id, COUNT( * ) + (select count(*) from items where me.category_id = category_id and registered < '2013-05-01 00:00:00') AS Value FROM items me WHERE ( ( category_id IS NOT NULL AND (me.registered BETWEEN '2013-05-01 00:00:00' AND '2013-05-02 00:00:00') ) ) GROUP BY registered, category_id
It takes almost 28 secs to generate the result. How can i increase the performance.
Update I am using pgSQL. The registered and category_id is indexed. There are 568814 rows. I will be also very grateful, if any one can tell me that my sql is correct or not.
Update Sorry for late reply.
CREATE TABLE items ( id uuid NOT NULL DEFAULT uuid_generate_v4(), registered timestamp without time zone NOT NULL, modified timestamp without time zone NOT NULL, published timestamp without time zone, owner_id uuid, status_id integer NOT NULL, category_id uuid NOT NULL, location_id uuid, title text, description text, quantity integer NOT NULL DEFAULT 1, unit text, price_low bigint, price bigint, currency text NOT NULL DEFAULT 'BDT'::text, ip text, attributes_json text, from_mobile boolean NOT NULL DEFAULT false, CONSTRAINT items_pkey PRIMARY KEY (id), CONSTRAINT items_fk_category_id FOREIGN KEY (category_id) REFERENCES categories (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT items_fk_location_id FOREIGN KEY (location_id) REFERENCES locations (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT items_fk_owner_id FOREIGN KEY (owner_id) REFERENCES users (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT items_fk_status_id FOREIGN KEY (status_id) REFERENCES item_statuses (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE )
CREATE TABLE items ... ;statement. Also see this for how to show the
EXPLAIN(also called execution or query) plan.
varchar? Please tell me it's actually a