here's the query you submitted, re-written to be slightly less insanely
difficult to read (no offence to you personally, but how you, and so many other
people like you, can work like that, i'll never know) --
select contacts.first_name
, contacts.middle_name
, contacts.last_name
, addresses.h_city
, contacts.email1
from contacts
, addresses
, contacts_addresses
, trips
, contacts_trips
where trips.premium > 100
and trips.premium < 201
and contacts.id=contacts_addresses.contact_id
and contacts_addresses.address_id=addresses.id
and contacts.id=contacts_trips.contact_id
and contacts_trips.trip_id=trips.id
here's the query re-written with JOIN syntax, which you should always use
instead of the dreaded "comma list" syntax --
select contacts.first_name
, contacts.middle_name
, contacts.last_name
, addresses.h_city
, contacts.email1
from contacts
inner
join contacts_addresses
on contacts_addresses.contact_id = contacts.id
inner
join addresses
on addresses.id = contacts_addresses.address_id
inner
join contacts_trips
on contacts_trips.contact_id = contacts.id
inner
join trips
on trips.id = contacts_trips.trip_id
and trips.premium > 100
and trips.premium < 201
the thing that jumps out at me immediately is that you seem to be joining
contacts to *two* many-to-many relationships
this will have multiplicative or what is know as "cross join" effects
e.g. if a given contact has 3 addresses and 5 trips, then this query would
return 15 rows for that contact
but the performance question usually hinges on the use of indexes
all of the following columns should either be a primary key or have an index
declared for them --
contacts.id
contacts_addresses.contact_id
contacts_addresses.address_id
addresses.id
contacts_trips.contact_id
contacts_trips.trip_id
trips.id
do they?
rudy
http://r937.com/
____ The WDVL Discussion List from WDVL.COM ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: [email protected]
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
