SQL HELP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

SQL HELP

Post 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'))
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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]
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Post Reply