Help with MySQL Joins

[EcLip_e]

Hey everyone,

I'm having some trouble getting my head around the Join statement in MySQL. I have two tables:

• online_clients, and
• dafif_arpt

The dafif_arpt table has over 13,000 entries, and although my join statement works, it's kind of slow – taking on average 10 seconds to complete within PHP. Here is my SQL statement. I'm currently trying to match two fields from online_clients with dafif_arpt. The only way I could figure that out, is by using two JOIN statements. When I do it this way though, MySQL pretty much goes through an infinite loop, and returns over 2 million entries:

SELECT oc.callsign, oc.cid, oc.realname, oc.latitude, oc.longitude, oc.altitude, 
oc.groundspeed, oc.heading, oc.planned_aircraft, oc.planned_flighttype, 
oc.planned_remarks, oc.planned_route, oc.planned_depairport, 
oc.planned_destairport, oc.time_login, da.wgs_dlat AS planned_depairport_lat, 
da.wgs_dlong AS planned_depairport_lon
FROM `online_clients` AS `oc` 
LEFT JOIN `dafif_arpt` AS `da` ON oc.planned_depairport IN (da.icao, da.faa_host_id)
LEFT JOIN `dafif_arpt` AS `da2` ON oc.planned_depairport IN (da.icao, da.faa_host_id)
WHERE oc.clienttype = 'pilot' AND oc.latitude != 0 AND oc.longitude != 0

I have gotten some better performance out of giving indexes for the more critical searches, however, I think that it can still be faster.

Thanks,

[Drakla]

I think you don't really want left joins, as you don't want all online clients returned for every query, just the ones matching the criterial. Something like this seems more plausible

SELECT oc.callsign, oc.cid, oc.realname, oc.latitude, oc.longitude, oc.altitude, 
oc.groundspeed, oc.heading, oc.planned_aircraft, oc.planned_flighttype, 
oc.planned_remarks, oc.planned_route, oc.planned_depairport, 
oc.planned_destairport, oc.time_login, da.wgs_dlat AS planned_depairport_lat, 
da.wgs_dlong AS planned_depairport_lon
FROM
	`online_clients` AS `oc`,
	`dafif_arpt` AS `da`
WHERE
	oc.clienttype = 'pilot' AND oc.latitude != 0 AND oc.longitude != 0
	AND oc.planned_depairport IN (da.icao, da.faa_host_id)

EDIT: can you state what you're trying to do with the query, that's often the easiest way.