Re: slow performance due to OR condition on separate tables

[Jose-Miguel Pulido]

Hi Rick,

I have posted the Q below on a separate forum. Although not related to full-text searching, you seem a knowledgeable person, and it would be great if you could provide any suggestions you may have.

I did not send you email directly because your email address is not visible.

Thanks again!

JM


--------------------------------

Hi all,

I have tables products, product_locations and locations. Each product may be available in multiple locations, and each location may have multiple products.

Given a location specified by latitude/longitude coordinates, I want to find all products closest to that location within a certain radius.

It is possible that, within that radius, there is more than one location per product. I only want to select one location, the closest one.

If I use this query, I get multiple locations per product_id:

SELECT
DISTINCT p.product_id,
(function_here_for_distance_based_on_lat_long) as distance
FROM
products p, product_locations pl, locations l
WHERE
p.product_id = pl.product_id
AND
pl.location_id = l.location_id
AND
distance < radius_value
ORDER BY distance

I tried grouping by product_id (to ensure only the first one is selected), but sometimes the query becomes empty, even though there are locations:

SELECT
DISTINCT p.product_id,
(function_here_for_distance_based_on_lat_long) as distance
FROM
products p, product_locations pl, locations l
WHERE
p.product_id = pl.product_id
AND
pl.location_id = l.location_id
GROUP BY p.product_id
HAVING distance < radius_value
ORDER BY distance


There is a requirement of being able to achieve this in a single query. Any help appreciated.

Thanks

JM