MySQL Forums
Forum List  »  Full-Text Search

Re: slow performance due to OR condition on separate tables
Posted by: Jose-Miguel Pulido
Date: December 03, 2009 04:09AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: slow performance due to OR condition on separate tables
2695
December 03, 2009 04:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.