optimizing MySQL table joins

crmpicco · January 26th, 2006, 08:43 AM

Script: Classic ASP

Code:

set rs = con.execute("SELECT DISTINCT(country.CountryCode), picco_air.countryname FROM picco_country country, picco_air dboair WHERE country.CountryCode = dboair.CountryCode AND country.Geog_loc = '"& region &"' ORDER BY dboair.CountryName ASC", ,adCmdText)

Is this the best way to run a joined query on these two tables?
My script is running slower than normal. My tables are `picco_air` and `picco_country`. I am setting aliases for both, dboair and country respectively.

Any ideas are appreciated.

Picco

www.crmpicco.co.uk

ChrisScott · January 26th, 2006, 10:34 AM

Hi Picco,

I would make the query something like this...

Code:

SELECT DISTINCT country.CountryCode, dboair.countryname FROM picco_country country 
INNER JOIN picco_air dboair ON country.CountryCode = dboair.CountryCode
WHERE country.Geog_loc = 'YourRegionHere' 
ORDER BY dboair.CountryName ASC;

I assume the country.CountryCode field and dboair.CountryCode are both indexed in some way.

If the query is still slow, I would index the Geog_loc field.

HTH,

Chris

crmpicco · January 26th, 2006, 01:48 PM

hi chris,
when i ran my version of the query MySQL CC gave a query time of 0.44 secs, yours was 0.87 secs

www.crmpicco.co.uk

crmpicco · January 27th, 2006, 04:59 AM

i also already have 3 indexes on the table, i wouldnt think having any more would be a good idea?

www.crmpicco.co.uk

ChrisScott · January 27th, 2006, 05:10 AM

Hi Picco,

There should be no real difference in query times as the two join types are equivalent and the only difference between the queries.

Have you got indexes on the countrycode fields?

How slow is your script running - the times for your query are not that high?

Cheers,

Chris

crmpicco · January 27th, 2006, 11:22 AM

ChrisScott · January 27th, 2006, 11:31 AM

Is this is the picco_air table?

Can you post an explain of the other table too?

Cheers,

Chris

crmpicco · January 31st, 2006, 10:54 AM

thanks chris, script seems to be running fine now

www.crmpicco.co.uk