Обсуждение: create index

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

many thx!

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