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

Code: Select all

tbl_task.t_status in(0,1,2)
[/edit]

Posted: Fri Dec 19, 2003 3:26 pm
by Weirdan
also you can take into account that

Code: Select all

A||B==!!(A||B)==!(!A&&!B)
I doubt it can speed up your query though.