Optimized mySQL - is this correct?

[ljmks2]

Hello!

I have read some posts here, but couldn't find the answer to my problem.

I am working on motorbike company page and i'm developing some temproary solution for so called "dealer locator".

The problem is that it (also) works with US zip codes and since there are many (around 30000) my queryes are pretty much slow!

I have two tables:

DEALERS
- id
- name, address,....
- zip

ZIPS
- id
- zip
- state (e.g. CO)
- full name of state (Connecticut)
- longitude
- latitude
- ... (two more)

My query looks something like this:
SELECT zip, abs(latitude-$latitude)....... AS distance FROM zips,dealers WHERE zip.id = dealers.zip ORDER BY distance

(THIS IS NOT EXACTLY THE CODE! I made it up right now, because i cannot connect to the f****** server or my computer at work. The point is that the syntax is correct)

Everything is OK just that this works very slow.

Would it make a difference if i'd make another table just with longitude,latitude (with which i calculate distance between states) and then select other informations (like zip code, state, full name of the state,...)?

Thank you everyone so much!

Lenart

[[deleted]]

First we'd need to see the actual query.

Queries can become much faster if the tables you are using are indexed. are they indexed?

[ljmks2]

This is my SQL query. The table is not indexed because i don't (yet) know how and when to use indexes.

$sql = "SELECT
dealers.name,dealers.address,dealers.city,dealers.zip,dealers.state,dealers.phone,dealers.fax,dealers.web,ABS(longitude-" . $zlongitude . ") + ABS(latitude-". $zlatitude .") AS distance FROM zips INNER JOIN dealers ON (zips.zip = dealers.zip) ORDER BY distance LIMIT 3";

Lenart

[[deleted]]

about indexing, read this:
http://www.phpbuilder.com/columns/tim20010110.php3