Slow query

[CassidyHunt]

I am using mysql 3. something and this seems to take a ton of time even though I am only pulling 10 records. Any suggestions

select b.miva_code,a.pc_make,a.year,a.category,c.description as "make_desc",a.subcategory,b.condition_code,a.record_id,b.product_cost,b.product_price,a.product_name,b.composite_description,a.prestige,a.hue_code,d.description as "cat_desc",e.sub_desc as "sub_desc",a.filename_tn,a.filename_fs from mastercatalog a, productdetails b, makes c, categories d, subcategories e where a.record_id = b.record_id and c.pc_make = a.pc_make and d.cat_code = a.category and e.sub_code = a.subcategory

Thanks

Cassidy

 

[nego78]

Do you have indexes on:
b.record_id
c.pc_make
d.cat_code
e.sub_code
?

Via-Net - web directory

 

[CassidyHunt]

I do on my local database where I initially wrote the query. I passed it to the ISP and this is where it is slow. I am normally an Oracle user and I would write this extremly different in oracle with derived tables and nested selects. I do not know if MySQL 3.x supports that.

Can you tell me if that would help?

 

[r937]

derived tables and nested selects work only in 4.1

r937.com | rudy.ca

 

[CassidyHunt]

Would there be a speed advantage to using a join?

 

[r937]

dunno about the speed advantage, but i can tell you that joins are probably the best way to get data out of related tables like these ...

from mastercatalog a, productdetails b, makes c, categories d, subcategories e

r937.com | rudy.ca