mysql - using "AND" and "OR" in an SQL query -

i trying optimize query. there 3 different lookups in table, think, not required.

this original query, want optimize.

select distinct(productid) q_products       visibility=1 , status=0 , productid in (productidlist)         union        select distinct(q_products.productid) q_products,q_product_apply_access          q_products.productid=q_product_apply_access.productid          , q_product_apply_access.partnerid='partnerid'         , q_product_apply_access.apply_status=0          , q_products.visibility=3 , q_products.status=0          , q_products.productid in (productidlist)         union       select distinct(q_products.productid) q_products,q_product_aff_access          q_products.productid=q_product_aff_access.productid          , q_product_aff_access.accid='accountid'          , q_products.visibility=2 , q_products.status=0          , q_products.productid in (productidlist) 

let me walk through query,

1) selecting distinct productid q_products have visibility=1 , status=0 , in productidlist

2) selecting distinct productid q_products , checking if same productid present in table q_product_apply_access, if so, checking if q_product_apply_access.partnerid same given , if q_product_apply_access.apply_status 0 , again, checking if productids in productidlist.

3) similar things , @ end checks again if productids in productidlist.

my idea optimize following way ->

1) select productids q_products in productidlist.

2) check if productids in tables q_product_apply_access , q_product_aff_access , if yes, subsequent conditions fulfilled.

3) when done, check of have q_products.visibility in (1,2,3) , q_products.status=0.

i think in way can minimize lookups in table , query queried in minimal subset of productidlist may contain upto 20-30 rows.

here code , not getting correct answer. can tell me how can it? or how should apply nested loops here?

select distinct(productid)          q_products oc,          q_product_apply_access  ocaa,          q_product_aff_access ocfa         productid in (productidlist)           , (             (oc.productid=ocaa.productid              , ocaa.affiliateid='partnerid'             , ocaa.apply_status=0)              or             (oc.productid=ocfa.productid              , ocfa.accid='accountid')             )           , status=0           , visibility in (1,2,3)  

the title of question:

using "and" , "or" in sql query

might more accurately defined as:

will using "and" and/or "or" statements query run faster?

and, short answer is, no.-- optimizations need done elsewhere.

so, long answer:

after looking @ while, requirements 'baked into' original query prevent logical joins speeding up. best bet run faster utilize indexes on tables being referenced. original answer using inner-selects, didn't meet requirements. requirements, mean necessity of returning list of unique productids productid table meeting 3 different , distinct criteria.

that said, re-write original query more readable, using join statements , aliases readability (note absence of distinct -- union ensures unique records out of 3 result sets):

select p.productid q_products p visibility = 1   , status = 0   , productid in (productidlist)  union  select p.productid q_products p join q_product_apply_access paa on paa.productid = p.productid paa.partnerid = 'partnerid'   , paa.apply_status = 0   , p.visibility = 3   , p.status = 0   , p.productid in (productidlist)  union  select p.productid q_products p join q_product_aff_access paf on paf.productid = p.productid paf.accid = 'accountid'   , p.visibility = 2   , p.status = 0   , p.productid in (productidlist) 

original answer

so, problem you're having bit difficult it's hard wrap head around original query in first place. said, threw possible solution using inner-selects joins q_products table. haven't tested it, let me know how turns out:

select *     q_products p     join (         select productid q_product_apply_access paa         paa.productid = p.productid            , paa.partnerid = 'partnerid'           , paa.apply_status = 0           , p.visibility = 3     ) paa on paa.productid = p.productid     join (         select productid q_product_aff_access paf         paf.productid = p.productid           , paf.accid = 'accountid'           , p.visibility = 2     ) paf on paf.productid = p.productid     p.status = 0     , p.productid in ( productidlist )