Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
|
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 26th, 2006, 08:43 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default optimizing MySQL table joins - script running slow

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
__________________
_______________________
Ayrshire Minis - a Mini E-Community
http://www.ayrshireminis.com
http://www.crmpicco.co.uk
 
Old January 26th, 2006, 10:34 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old January 26th, 2006, 01:48 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

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
 
Old January 27th, 2006, 04:59 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

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

www.crmpicco.co.uk
 
Old January 27th, 2006, 05:10 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old January 27th, 2006, 11:22 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

no there is no indexes on the countrycode fields:
+---------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| airportcode | char(3) | YES | MUL | | |
| airportname | varchar(100) | YES | | | |
| citycode | char(3) | YES | MUL | | |
| cityname | varchar(100) | YES | | | |
| countrycode | char(2) | YES | | [NULL] | |
| provincecode | char(2) | YES | | [NULL] | |
| provincename | varchar(100) | YES | | | |
| provincecountrycode | char(2) | YES | | [NULL] | |
| countryname | varchar(100) | YES | MUL | | |
+---------------------+--------------+------+-----+---------+-------+
that is an explain of my table

www.crmpicco.co.uk
 
Old January 27th, 2006, 11:31 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Is this is the picco_air table?

Can you post an explain of the other table too?

Cheers,

Chris



 
Old January 31st, 2006, 10:54 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

thanks chris, script seems to be running fine now

www.crmpicco.co.uk





Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple sql joins in mySQL 5 Dean Lovell SQL Language 4 January 19th, 2007 02:43 PM
HELP -- Slow SP on MySQL hahahardididi SQL Language 0 April 27th, 2006 05:47 AM
HELP --- Slow SP on MySQL hahahardididi MySQL 0 April 27th, 2006 05:47 AM
Linked Table working very slow with client program Nerijus Access 0 July 7th, 2003 08:34 AM
SQL Table Joins Dredd Classic ASP Databases 2 June 3rd, 2003 06:24 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.