select and or help

[woompy]

I need help with this table join.
What i'm using at the moment is this select table join statement which works fine.

SELECT A.id,A.mem_id,C.category,B.subcat,D.country,A.state,A.city,A.acode,A.international,A.title,A.description,A.slocation,A.url,A.phone,DATE_FORMAT(A.date_added, '%D %b %Y'),A.d_date,A.site,A.graphic,A.trader,A.glob FROM ads as A, category as C, subcat as B, country as D WHERE A.category_id=C.cat_id AND A.subcat_id=B.subcat_id AND A.country_id=D.id AND A.subcat_id='13' AND A.country_id='1'

This is the results of explain select, it's using country_id as the index key for A. -
A ref country_id,category_id,subcat_id country_id 2 const 1 where used
C eq_ref PRIMARY PRIMARY 2 A.category_id 1
B eq_ref PRIMARY PRIMARY 2 A.subcat_id 1
D eq_ref PRIMARY PRIMARY 2 A.country_id 1

What i need to do is to add an || condition to the statement.

SELECT A.id,A.mem_id,C.category,B.subcat,D.country,A.state,A.city,A.acode,A.international,A.title,A.description,A.slocation,A.url,A.phone,DATE_FORMAT(A.date_added, '%D %b %Y'),A.d_date,A.site,A.graphic,A.trader,A.glob FROM ads as A, category as C, subcat as B, country as D WHERE A.category_id=C.cat_id AND A.subcat_id=B.subcat_id AND A.country_id=D.id AND A.subcat_id='13' AND ( A.glob='1' OR A.country_id='2')

This works ok and pulls the correct records but it won't use an index for A.
This is explain select for that statement -
A ALL country_id,category_id,glob,subcat_id \N \N \N 7 where used
C eq_ref PRIMARY PRIMARY 2 A.category_id 1
B eq_ref PRIMARY PRIMARY 2 A.subcat_id 1
D eq_ref PRIMARY PRIMARY 2 A.country_id 1

Is there another way of doing this with the || condition or am I stuck with it.
If I remove the () from the where condition it crashes mysql.

thanks
Bob

http://totallyfreeads.com.au

 

[r937]

FROM ads as A
, category as C
, subcat as B
, country as D
WHERE A.category_id=C.cat_id
AND A.subcat_id=B.subcat_id
AND A.country_id=D.id
AND A.subcat_id='13'
AND ( A.glob='1' OR A.country_id='2' )

maybe put an additional index on glob? or put glob as a secondary column on an existing index like country?

rudy

 

[woompy]

Thanks for the reply.
Glob is indexed, explain select says that possible keys are country_id,category_id,glob,subcat_id but its not using any.
I've been trying to force it to use a key with use index -

SELECT A.id,A.mem_id,C.category,B.subcat,D.country,A.state,A.city,A.acode,A.international,A.title,A.description,A.slocation,A.url,A.phone,DATE_FORMAT(A.date_added, '%D %b %Y'),A.d_date,A.site,A.graphic,A.trader,A.glob FROM ads as A, category as C, subcat as B, country as D use index (country_id) WHERE A.category_id=C.cat_id AND A.subcat_id=B.subcat_id AND A.country_id=D.id AND A.subcat_id='13' AND ( A.glob='1' OR A.country_id='2')

but it keeps giving me an 'Key column 'country_id' doesn't exist in table' error.

I'll try a double index on glob and country_id.

Thanks
Bob

http://totallyfreeads.com.au

 

[r937]

i have never had to to tune a mysql query, so i have never used use index (country_id) but i'll betcha fer sure it's looking for an index name instead of a column name

rudy

 

[woompy]

Thanks r973,
That is the index name. If you don't give an index a name then it defaults to the column name.

thanks
Bob

http://totallyfreeads.com.au