Page 1 of 1
SQL HELP
Posted: Fri Dec 19, 2003 10:07 am
by gurjit
any way i can write this query simpler, it takes a long time to run beacuse of the OR inside:
select distinct(tid) from tbl_website,tbl_website_section,tbl_section_ref,tbl_website_section_second_level,tbl_section_ref_second_level,tbl_task where tbl_task.frn_wid = tbl_website.wid AND ((tbl_task.t_status = 0 or tbl_task.t_status = 1 or tbl_task.t_status = 2) and tbl_task.frn_wid = $wid) AND ((tbl_task.frn_wsid = tbl_website_section.wsid and tbl_website_section.wsid = tbl_website_section_second_level.frn_wsid and tbl_website_section_second_level.frn_sl_ref = '$the_ref') OR (tbl_task.frn_sfid = tbl_section_ref.sfid and tbl_website_section.wsid = tbl_section_ref.frn_wsid and tbl_section_ref_second_level.frn_sfid = tbl_section_ref.sfid and tbl_section_ref_second_level.frn_sl_ref = '$the_ref'))
Posted: Fri Dec 19, 2003 10:08 am
by malcolmboston
just making it easier to read
Code: Select all
select distinct(tid) from tbl_website,tbl_website_section,tbl_section_ref,tbl_website_section_second_level,tbl_section_ref_second_level,tbl_task where tbl_task.frn_wid = tbl_website.wid AND ((tbl_task.t_status = 0 or tbl_task.t_status = 1 or tbl_task.t_status = 2) and tbl_task.frn_wid = $wid) AND ((tbl_task.frn_wsid = tbl_website_section.wsid and tbl_website_section.wsid = tbl_website_section_second_level.frn_wsid and tbl_website_section_second_level.frn_sl_ref = '$the_ref') OR (tbl_task.frn_sfid = tbl_section_ref.sfid and tbl_website_section.wsid = tbl_section_ref.frn_wsid and tbl_section_ref_second_level.frn_sfid = tbl_section_ref.sfid and tbl_section_ref_second_level.frn_sl_ref = '$the_ref'))
you really should think bout structuring your code better
Posted: Fri Dec 19, 2003 2:32 pm
by Weirdan
just making it even easier

to read
original:
Code: Select all
select
distinct(tid)
from
tbl_website,
tbl_website_section,
tbl_section_ref,
tbl_website_section_second_level,
tbl_section_ref_second_level,
tbl_task
where
tbl_task.frn_wid = tbl_website.wid
AND (
(tbl_task.t_status = 0 or tbl_task.t_status = 1 or tbl_task.t_status = 2)
and tbl_task.frn_wid = $wid
)
AND (
(tbl_task.frn_wsid = tbl_website_section.wsid
and tbl_website_section.wsid = tbl_website_section_second_level.frn_wsid
and tbl_website_section_second_level.frn_sl_ref = '$the_ref')
OR (
tbl_task.frn_sfid = tbl_section_ref.sfid
and tbl_website_section.wsid = tbl_section_ref.frn_wsid
and tbl_section_ref_second_level.frn_sfid = tbl_section_ref.sfid
and tbl_section_ref_second_level.frn_sl_ref = '$the_ref'
)
)
[edit]
edited:
Code: Select all
select
distinct(tid)
from
tbl_website,
tbl_website_section,
tbl_section_ref,
tbl_website_section_second_level,
tbl_section_ref_second_level,
tbl_task
where
tbl_task.frn_wid = tbl_website.wid
and tbl_task.t_status in(0,1,2)
and tbl_task.frn_wid = $wid
AND (
(tbl_task.frn_wsid = tbl_website_section.wsid
and tbl_website_section.wsid = tbl_website_section_second_level.frn_wsid
and tbl_website_section_second_level.frn_sl_ref = '$the_ref')
OR (
tbl_task.frn_sfid = tbl_section_ref.sfid
and tbl_website_section.wsid = tbl_section_ref.frn_wsid
and tbl_section_ref_second_level.frn_sfid = tbl_section_ref.sfid
and tbl_section_ref_second_level.frn_sl_ref = '$the_ref'
)
)
this line
Code: Select all
(tbl_task.t_status = 0 or tbl_task.t_status = 1 or tbl_task.t_status = 2)
is replaced with
[/edit]
Posted: Fri Dec 19, 2003 3:26 pm
by Weirdan
also you can take into account that
I doubt it can speed up your query though.