Jump to content


Photo

selecting fields from diffrent tables


  • Please log in to reply
3 replies to this topic

#1 khan kaka

khan kaka
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts
  • Locationaustralia

Posted 27 November 2005 - 07:33 AM


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.
khan kaka

#2 Honoré

Honoré
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationAntwerp - Belgium

Posted 27 November 2005 - 08:10 AM

[!--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) View Post[/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.
[/quote]

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.


#3 khan kaka

khan kaka
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts
  • Locationaustralia

Posted 27 November 2005 - 08:35 AM

[!--quoteo(post=322409:date=Nov 27 2005, 03:10 AM:name=Honoré)--][div class=\'quotetop\']QUOTE(Honoré @ Nov 27 2005, 03:10 AM) View Post[/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.
[/quote]


i tried it its still very very very slow.
how can i index my tables?


khan kaka

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 28 November 2005 - 09:29 PM

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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users