Geographic search

I have this table (inradar_ad) with almost 300k entries. I want to know why my query takes 160 secs to run.

I tried limiting with LIMIT 10 to see if I get a speed boost, I but didn't. Does LIMIT speed anything up? I don't think so in this case, since it has to calculate everything and then limit it.

``
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=root@localhost PROCEDURE SearchAdsOrderedByGroupThenLocation(IN basic_user_id INT, IN mylat DOUBLE, IN mylon DOUBLE, IN max_dist INT, IN q VARCHAR(255))
BEGIN
DECLARE lon1 FLOAT;
DECLARE lon2 FLOAT;
DECLARE lat1 FLOAT;
DECLARE lat2 FLOAT;

SET @group_id = (SELECT group_id from basicuser where id = basic_user_id);
SET @subgroup_id = (SELECT subgroup_id from basicuser where id = basic_user_id);
SET @tertiarygroup_id = (SELECT tertiarygroup_id from basicuser where id = basic_user_id);

-- get the original lon and lat for the userid
SET lon1 = mylon - max_dist / abs(cos(radians(mylat)) * 69);
SET lon2 = mylon + max_dist / abs(cos(radians(mylat)) * 69);
SET lat1 = mylat - (max_dist / 69);
SET lat2 = mylat + (max_dist / 69);

SELECT DISTINCT inradar_ad., 3956 2 ASIN(SQRT(POWER(SIN((orig.latitude - dest.latitude) pi()/180 / 2), 2) + COS(orig.latitude pi()/180) COS(dest.latitude pi()/180) POWER(SIN((orig.longitude - dest.longitude) * pi()/180 / 2), 2))) as distance
FROM
location AS dest
LEFT OUTER JOIN inradar_ad ON (inradar_ad.location_id = dest.id)
LEFT OUTER JOIN inradar_ad_company ON (inradar_ad.id = inradar_ad_company.inradarad_ptr_id)
LEFT OUTER JOIN inradar_ad_person ON (inradar_ad.id = inradar_ad_person.inradarad_ptr_id`)

If after adding the index on latitude and longitude you still see poor performance, trim the query down to the bare minimum to isolate the source. Remove all of the joins and the where clause referencing those tables. Once it's doing just the distance calculation (which should be move to a deterministic stored function), you can get a better time measurement.

On an unrelated note, you can set the three variables at the top with a single select statement using into.

SET @group_id = (SELECT group_id from basicuser where id = basic_user_id);
SET @subgroup_id = (SELECT subgroup_id from basicuser where id = basic_user_id);
SET @tertiarygroup_id = (SELECT tertiarygroup_id from basicuser where id = basic_user_id);

SELECT group_id, subgroup_id, tertiarygroup_id
FROM basicuser WHERE id = basic_user_id
INTO @group_id, @subgroup_id, @tertiarygroup_id;

StackExchange Code Review Q#52193, answer score: 6