Message-ID: <3DDA46F9.6060304@klaster.net>
Date: Tue, 19 Nov 2002 15:13:13 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: "ernest_it@hotmail.com" <ernest_it@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: create index
References: <10d6eb7c.0211182244.ec57100@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Uz.ytkownik ernest_it@hotmail.com napisa?:
> i don't know how to create the index about the following statement.
> 
> 
> SELECT b.screen_id AS screen_id, b.name AS screen_name
> FROM tbl_showlog AS a CROSS JOIN
> tbl_screen AS b CROSS JOIN
> tbl_company AS c
> WHERE a.screen_id = b.screen_id
> AND b.company_id = c.company_id
> AND c.company_id = 1
> AND c.is_active = 1
> GROUP BY b.screen_id, b.name
> ORDER BY b.screen_id
Change your query to explicit joins. It looks much better and explains 
directly your problem.

Example:
SELECT screen_id, B.name as screen_name
FROM
  tbl_company C
  join tbl_screen B using (company_id)
  join tbl_showlog A using (screen_id)
WHERE
  company_id=1 and is_active=1


I don't know how many records do you have, but try to create indexes:
1. tbl_company -> company_id
2. tbl_screen -> company_id
3. tbl_showlog -> screen_id


Regards,
Tomasz Myrta