selecting fields from diffrent tables

[khan kaka]

 

i want to display only the records that mach from two tables i have used this mysql code but it takes toolong to show the records it takes more then few min.

 

SELECT company.name, routes.name

FROM company, routes

WHERE company.name = routes.name

ORDER BY routes.name

 

i have 16000 records in routes table and 3000 records in company table.

 

what i want to do is i want the mysql to take the name of company from routes table and compare it wiht company.name table .

 

if the same company name exists in company.name table display.

 

and when it takes few min and displays the records it shows i have 28558 records.

 

in table routes i have the same company name repeated few times but the functions are diffrent.

 

how can i solve this problem and make it work faster its very very slow now.

[Honoré]

[!--quoteo(post=322407:date=Nov 27 2005, 09:33 AM:name=khan kaka)--][div class=\'quotetop\']QUOTE(khan kaka @ Nov 27 2005, 09:33 AM) 322407[/snapback][/div][div class=\'quotemain\'][!--quotec--]

i want to display only the records that mach from two tables i have used this mysql code but it takes toolong to show the records it takes more then few min.

 

SELECT company.name, routes.name

FROM company, routes

WHERE company.name = routes.name

ORDER BY routes.name

 

i have 16000 records in routes table and 3000 records in company table.

 

what i want to do is i want the mysql to take the name of company from routes table and compare it wiht company.name table .

 

if the same company name exists in company.name table display.

 

and when it takes few min and displays the records it shows i have 28558 records.

 

in table routes i have the same company name repeated few times but the functions are diffrent.

 

how can i solve this problem and make it work faster its very very slow now.

 

As company.name and routes.name are identical in your query you only have to select one of them in your result set:

 

SELECT routes.name

FROM routes, company

WHERE company.name = routes.name

ORDER BY routes.name

 

The usage of DISTINCT can eliminate doubles in your result set:

SELECT DISTINCT routes.name

FROM routes, company

WHERE company.name = routes.name

ORDER BY routes.name

 

If possible, making routes.name and company.name indexes will improve the speed of your query.

 

[khan kaka]

[!--quoteo(post=322409:date=Nov 27 2005, 03:10 AM:name=Honoré)--][div class=\'quotetop\']QUOTE(Honoré @ Nov 27 2005, 03:10 AM) 322409[/snapback][/div][div class=\'quotemain\'][!--quotec--]

As company.name and routes.name are identical in your query you only have to select one of them in your result set:

 

SELECT routes.name

FROM routes, company

WHERE company.name = routes.name

ORDER BY routes.name

 

The usage of DISTINCT can eliminate doubles in your result set:

SELECT DISTINCT routes.name

FROM routes, company

WHERE company.name = routes.name

ORDER BY routes.name

 

If possible, making routes.name and company.name indexes will improve the speed of your query.

 

 

i tried it its still very very very slow.

how can i index my tables?

 

 

[ryanlwh]

use JOIN would make the query faster

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT routes.name

FROM company JOIN routes ON company.name = routes.name

ORDER BY routes.name [!--sql2--][/div][!--sql3--]