Geographic search

Question

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`)
 LEFT OUTER JOIN `inradar_category` ON (`inradar_ad`.`category_id` = `inradar_category`.`id`)
 LEFT OUTER JOIN `inradar_subcategory` ON (`inradar_ad`.`subcategory_id` = `inradar_subcategory`.`id`)
 LEFT OUTER JOIN `basicuser` ON (`inradar_ad`.`owner_id` = `basicuser`.`id`)
 LEFT OUTER JOIN `auth_user` ON (`basicuser`.`user_id` = `auth_user`.`id`)
 LEFT OUTER JOIN `inradar_ad_multiple` ON (`inradar_ad`.`multiple_advertiser_id` = `inradar_ad_multiple`.`id`),
 location AS orig
 WHERE `inradar_ad`.`available` != 0 AND
 (
 (
 `inradar_ad_multiple`.`id` IS NULL AND
 (
 `inradar_ad_company`.`corporate_name` LIKE REPLACE('%$$**$$%', '$$**$$', q) OR
 `inradar_ad_person`.`name` LIKE REPLACE('%$$**$$%', '$$**$$', q) OR
 `inradar_category`.`name` LIKE REPLACE('%$$**$$%', '$$**$$', q) OR
 `inradar_subcategory`.`name` LIKE REPLACE('%$$**$$%', '$$**$$', q) OR
 `inradar_ad`.`description` LIKE REPLACE('%$$**$$%', '$$**$$', q) OR
 `inradar_ad`.`tags` LIKE REPLACE('%$$**$$%', '$$**$$', q) OR
 `auth_user`.`first_name` LIKE REPLACE('%$$**$$%', '$$**$$', q) OR
 `auth_user`.`last_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)
 )
 )
 ) AND
 `inradar_ad`.`available` = 1 AND
 dest.longitude BETWEEN lon1 AND lon2 AND dest.latitude BETWEEN lat1 AND lat2
 HAVING distance < max_dist ORDER BY
 CASE WHEN `basicuser`.`tertiarygroup_id` = @tertiarygroup_id THEN `basicuser`.`tertiarygroup_id` END DESC,
 CASE WHEN `basicuser`.`subgroup_id` = @subgroup_id THEN `basicuser`.`subgroup_id` END DESC,
 CASE WHEN `basicuser`.`group_id` = @group_id THEN `basicuser`.`group_id` END DESC,
 distance ASC;
END

Here's the table structure:

DROP TABLE IF EXISTS `location`;
CREATE TABLE `location` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `address` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_number` int(11) DEFAULT NULL,
 `address_complement` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `neighborhood` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
 `zip_code` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
 `city_id` int(11) DEFAULT NULL,
 `state_id` int(11) DEFAULT NULL,
 `country_id` int(11) DEFAULT NULL,
 `latitude` double DEFAULT NULL,
 `longitude` double DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `location_b376980e` (`city_id`),
 KEY `location_5654bf12` (`state_id`),
 KEY `location_d860be3c` (`country_id`),
 CONSTRAINT `city_id_refs_id_ab743da9` FOREIGN KEY (`city_id`) REFERENCES `location_city` (`id`),
 CONSTRAINT `country_id_refs_id_8d58a0d2` FOREIGN KEY (`country_id`) REFERENCES `location_country` (`id`),
 CONSTRAINT `state_id_refs_id_9f4f2609` FOREIGN KEY (`state_id`) REFERENCES `location_state` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102346 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS `basicuser`;
CREATE TABLE `basicuser` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `added_by_id` int(11) DEFAULT NULL,
 `photo` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `user_id` int(11) NOT NULL,
 `mobile_phone` varchar(24) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(24) COLLATE utf8_unicode_ci NOT NULL,
 `group_id` int(11) DEFAULT NULL,
 `subgroup_id` int(11) DEFAULT NULL,
 `tertiarygroup_id` int(11) DEFAULT NULL,
 `location_id` int(11) DEFAULT NULL,
 `user_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `personal_data_filled` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `basicuser_user_id_70deebc304043f15_uniq` (`user_id`),
 KEY `basicuser_f6b8c251` (`added_by_id`),
 KEY `basicuser_6340c63c` (`user_id`),
 KEY `basicuser_5f412f9a` (`group_id`),
 KEY `basicuser_d6ee8a04` (`subgroup_id`),
 KEY `basicuser_9394537f` (`tertiarygroup_id`),
 KEY `basicuser_afbb987d` (`location_id`),
 CONSTRAINT `added_by_id_refs_id_1a91b691` FOREIGN KEY (`added_by_id`) REFERENCES `basicuser` (`id`),
 CONSTRAINT `group_id_refs_id_cd2e32a4` FOREIGN KEY (`group_id`) REFERENCES `inradar_group` (`id`),
 CONSTRAINT `location_id_refs_id_a7ee2d0b` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
 CONSTRAINT `subgroup_id_refs_id_3c81d859` FOREIGN KEY (`subgroup_id`) REFERENCES `inradar_subgroup` (`id`),
 CONSTRAINT `tertiarygroup_id_refs_id_4f311f89` FOREIGN KEY (`tertiarygroup_id`) REFERENCES `inradar_tertirarygroup` (`id`),
 CONSTRAINT `user_id_refs_id_cb17b658` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=144 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `inradar_subgroup`
--
DROP TABLE IF EXISTS `inradar_subgroup`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `inradar_subgroup` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `group_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `inradar_subgroup_5f412f9a` (`group_id`),
 CONSTRAINT `group_id_refs_id_02dc2b6d` FOREIGN KEY (`group_id`) REFERENCES `inradar_group` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `inradar_ad`
--
DROP TABLE IF EXISTS `inradar_ad`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `inradar_ad` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `owner_id` int(11) DEFAULT NULL,
 `description` varchar(5000) COLLATE utf8_unicode_ci NOT NULL,
 `category_id` int(11) DEFAULT NULL,
 `subcategory_id` int(11) DEFAULT NULL,
 `video_url` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `logo` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `location_id` int(11) NOT NULL,
 `business_hours` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
 `subscription_plan_id` int(11) DEFAULT NULL,
 `tags` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
 `advertiser_occupation` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
 `advertiser_group_message` varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
 `email_contact_form` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
 `website` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `e_commerce` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `phone2` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `blap_phone` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 `delivery` tinyint(1) DEFAULT NULL,
 `comment_votes` int(11) NOT NULL,
 `comment_quantity` int(11) NOT NULL,
 `multiple_advertiser_id` int(11) DEFAULT NULL,
 `user_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `additional_info` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
 `advertiser_available` tinyint(1) NOT NULL,
 `used_free_coupom` tinyint(1) NOT NULL,
 `modified_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `available` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `inradar_ad_location_id_c75ed6fdef730d0_uniq` (`location_id`),
 UNIQUE KEY `multiple_advertiser_id` (`multiple_advertiser_id`),
 KEY `inradar_ad_cb902d83` (`owner_id`),
 KEY `inradar_ad_6f33f001` (`category_id`),
 KEY `inradar_ad_790ef9fb` (`subcategory_id`),
 KEY `inradar_ad_afbb987d` (`location_id`),
 KEY `inradar_ad_edafc3c2` (`subscription_plan_id`),
 CONSTRAINT `category_id_refs_id_d9b04586` FOREIGN KEY (`category_id`) REFERENCES `inradar_category` (`id`),
 CONSTRAINT `location_id_refs_id_a1d009d1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
 CONSTRAINT `multiple_advertiser_id_refs_id_8f1f14fc` FOREIGN KEY (`multiple_advertiser_id`) REFERENCES `inradar_ad_multiple` (`id`),
 CONSTRAINT `owner_id_refs_id_caece728` FOREIGN KEY (`owner_id`) REFERENCES `basicuser` (`id`),
 CONSTRAINT `subcategory_id_refs_id_b5f2fdc5` FOREIGN KEY (`subcategory_id`) REFERENCES `inradar_subcategory` (`id`),
 CONSTRAINT `subscription_plan_id_refs_id_76dc847a` FOREIGN KEY (`subscription_plan_id`) REFERENCES `inradar_subscription` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101552 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `inradar_tertirarygroup`
--
DROP TABLE IF EXISTS `inradar_tertirarygroup`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `inradar_tertirarygroup` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `subgroup_id` int(11) NOT NULL,
 `location_id` int(11) DEFAULT NULL,
 `phone` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`),
 KEY `inradar_tertirarygroup_d6ee8a04` (`subgroup_id`),
 KEY `inradar_tertirarygroup_afbb987d` (`location_id`),
 CONSTRAINT `location_id_refs_id_6d3145dd` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
 CONSTRAINT `subgroup_id_refs_id_c0dcd513` FOREIGN KEY (`subgroup_id`) REFERENCES `inradar_subgroup` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `inradar_ad_multiple`
--

Just in case you need to know, I call it like this:

call SearchAdsOrderedByGroupThenLocation(40, -22.4169605, -42.9756016, 100, 'a');

Since creating an INDEX to latitude and longitude on Location table did not do me so much good, here's the output of the EXPLAIN SELECT command:

'1','SIMPLE','dest','range','PRIMARY,location_latlng','location_latlng','18',NULL,'3445','Using where; Using index; Using temporary; Using filesort'
'1','SIMPLE','inradar_ad','eq_ref','inradar_ad_location_id_c75ed6fdef730d0_uniq,inradar_ad_afbb987d','inradar_ad_location_id_c75ed6fdef730d0_uniq','4','inradar_db.dest.id','1','Using where'
'1','SIMPLE','inradar_ad_company','eq_ref','PRIMARY','PRIMARY','4','inradar_db.inradar_ad.id','1',NULL
'1','SIMPLE','inradar_ad_person','eq_ref','PRIMARY','PRIMARY','4','inradar_db.inradar_ad.id','1',NULL
'1','SIMPLE','inradar_category','eq_ref','PRIMARY','PRIMARY','4','inradar_db.inradar_ad.category_id','1',NULL
'1','SIMPLE','inradar_subcategory','eq_ref','PRIMARY','PRIMARY','4','inradar_db.inradar_ad.subcategory_id','1',NULL
'1','SIMPLE','basicuser','eq_ref','PRIMARY','PRIMARY','4','inradar_db.inradar_ad.owner_id','1',NULL
'1','SIMPLE','auth_user','eq_ref','PRIMARY','PRIMARY','4','inradar_db.basicuser.user_id','1','Using where'
'1','SIMPLE','inradar_ad_multiple','eq_ref','PRIMARY','PRIMARY','4','inradar_db.inradar_ad.multiple_advertiser_id','1','Using where; Not exists; Using index'
'1','SIMPLE','orig','index',NULL,'location_latlng','18',NULL,'100816','Using index; Using join buffer (Block Nested Loop)'

Answer 1

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;

• Patrick Bassut: When encapsulating the part where the distance calculation occurs inside a function it gave me a .2 sec latency more. Weird, right?
• David Harkness: While it's possible calling the function could add slightly more time to the query, I wouldn't expect it to be 200ms. It should only call it for the rows in the bounding box.

Answer 2

I see no index on the latitude and longitude columns of the location table. Try

CREATE INDEX location_latlng ON `location` (`latitude`, `longitude`);

If it's still slow, then add the output of EXPLAIN SELECT to your question.

• Patrick Bassut: added the explain output
• Patrick Bassut: Turns out, I messed up the test. The indexing did work. After doing a lot of modification in the query it was no longer functioning well. Tested with a higher radius, still having a very high latency(23 sec). And with the 100km radius I'm currently using, 0.5 secs. I mean, I don't know if someday I'll need to increase the radius. But I think when the time comes I'll need to think of something else. Am I right about this? Or from 100 to 1k things got just too bad than it actually should?
• Patrick Bassut: I take back what I said about the query getting optimized. It did a little, not much. 12 secs.

Answer 3

I was hoping that adding an index would adequately address the performance problems, because a full review would be much more involved. Since that did not do the trick, here is the long answer...

The order in which to apply the search criteria

Your query wants to limit the inradar_ad results by five criteria:

1. inradar_ad must be available.
2. inradar_ad has no associated inradar_ad_multiple.
3. inradar_ad's location ("dest") must be within a bounding box that is circumscribed around a circle of radius max_dist that is centered at (mylat, mylon).
4. inradar_ad's location ("dest") must be within a radius of max_dist from some existing location ("orig").
5. Wildcard text search must match at least one of the following:
   • inradar_ad has a company whose corporate_name matches
   • inradar_ad has a person whose name matches
   • inradar_ad has a category whose name matches
   • inradar_ad has a subcategory whose name matches
   • inradar_ad has a matching description
   • inradar_ad has matching tags
   • inradar_ad has an owner whose first_name or last_name matches

I've listed them in order from fastest to slowest, assuming that the query is implemented optimally. Let's consider each one.

1. You've written both `inradar_ad`.`available` != 0 and `inradar_ad`.`available` = 1, which is redundant. The query might benefit from an index on the available column.

2. This anti-join can be relatively fast, assuming that inradar_ad_multiple is indexed by its primary key.

3. The bounding-box join can also be reasonably fast, if the appropriate index exists. As you can see, this is where that short answer fits into the big picture.

4. We should be able to make two optimizations:

   1. This criterion is currently specified in a HAVING clause, which means it has to be executed after all of the WHERE conditions. That is particularly unfortunate, considering that criterion 5 is hugely expensive.

   2. It's probably worthwhile to add a redundant bounding-box criterion for this join. In order for orig to be within a radius of max_dist from dest, it must also be within a bounding box of dest.latitude ± max_dist and dest.longitude ± max_dist (with the appropriate unit conversion of 1° ≲ 111.32 km ≲ 69.18 mi).

      This should provide a performance gain, mainly because the location_latlng index can be used, and secondarily because a bounding-box filter is less computationally intensive than the haversine formula for the great-circle distance.

5. All those joins seem unavoidable. More importantly, each wildcard text search is hugely expensive, being a byte-by-byte scan of every field. You might be able to implement full-text search to help.

Discussion about correctness

The search, as I read it, is for ads whose location ("dest") is within a bounding box that is circumscribed around a circle of radius max_dist that is centered at (mylat, mylon). Your bounding box fails if lon1 and lon2 straddle the 180° meridian. That's probably forgivable, if you don't plan to be operating in that part of the world. The dest must be within a circle of radius max_dist that is centered at some other ad's location ("orig"). Is this neighbour-of-a-neighbour-of-a-point comparison deliberate, or a mistake?

The ORDER BY clause is puzzling:

• It sorts by descending ids, which, assuming that the id columns are auto-incremented keys, just indicate the order in which the groups were created.
• The sort key is sometimes the tertiarygroup_id, sometimes the subgroup_id, sometimes the group_id, and sometimes NULL. Assuming that the IDs for tertiary groups, subgroups, and groups are independently auto-incremented, there's no reason that they can be meaningfully compared in any way.
• The group IDs aren't among the columns returned by the query.

You also seem to care about how these groups match up against the group IDs of the user specified in the parameter of the procedure. I suspect that what you had intended was to prioritize ads that are owned by a user that is in the same tertiary group as the given user, followed by ads that are owned by a user that is in the same subgroup, followed by ads in the same group.

SELECT DISTINCT

The next concern is the use of SELECT DISTINCT, which should be avoided, since the database would need to put work into deduplicating the results. A well crafted query against a properly normalized database should just produce the right results without requiring such deduplication. In other words, if you need SELECT DISTINCT, then either

• Your database schema is incorrectly normalized
• Your database contains junk data, which failed to be rejected by table constraints
• Your query is poorly crafted such that the JOINs produce duplicate rows

I believe the last explanation applies here. You perform a lot of LEFT OUTER JOINs for the text searches, but you aren't interested in the columns of the joined tables. Discarding the columns of the joined tables produces spurious rows.

Instead of LEFT OUTER JOIN, you should be using WHERE EXISTS clauses with correlated subqueries.

Suggested solution

This is untested code, but I believe it is equivalent to the original and applies the optimization principles stated above. Any corrections would be appreciated.

@pattern is REPLACE('%$$**$$%', '$$**$$', q).

SELECT `inradar_ad`.*
 , orig.distance
 FROM
 `inradar_ad`
 INNER JOIN location AS dest
 ON dest.id = `inradar_ad`.location_id
 INNER JOIN (
 SELECT latitude
 , longitude
 , 3956 * 2 * ASIN(
 SQRT(
 POWER(SIN((location.latitude - dest.latitude) * pi()/180 / 2), 2)
 + COS(location.latitude * pi()/180) * COS(dest.latitude * pi()/180)
 * POWER(SIN((location.longitude - dest.longitude) * pi()/180 / 2), 2)
 )
 ) AS distance
 FROM location
 ) AS orig
 ON orig.latitude BETWEEN (dest.latitude - max_dist / 69.18)
 AND (dest.latitude + max_dist / 69.18)
 AND (orig.longitude > dest.longitude - max_dist / 69.18 OR
 dest.longitude - max_dist / 69.18 < -180)
 AND (orig.longitude < dest.longitude + max_dist / 69.18 OR
 dest.longitude + max_dist / 69.18 > +180)
 INNER JOIN `basicuser` AS givenuser ON (givenuser.`id` = `inradar_ad`.`owner_id`)
 LEFT OUTER JOIN `basicuser` ON (`basicuser`.`id` = `inradar_ad`.`owner_id`)
 WHERE
 givenuser.id = basic_user_id
 AND `inradar_ad`.`available` = 1
 AND dest.latitude BETWEEN lat1 AND lat2
 AND dest.longitude BETWEEN lon1 AND lon2
 AND orig.distance < max_dist
 AND NOT EXISTS (
 SELECT `inradar_ad_multiple`.`id`
 FROM `inradar_ad_multiple`
 WHERE `inradar_ad_multiple`.`id` = `inradar_ad`.`multiple_advertiser_id`
 )
 AND (
 `inradar_ad`.`description` LIKE @pattern
 OR `inradar_ad`.`tags` LIKE @pattern
 OR EXISTS (
 SELECT `auth_user`.`id`
 FROM `auth_user`
 WHERE `auth_user`.`id` = `basic_user`.`user_id`
 AND `first_name` LIKE @pattern OR `last_name` LIKE @pattern
 )
 OR EXISTS (
 SELECT `inradar_ad_company`.`inradarad_ptr_id`
 FROM `inradar_ad_company`
 WHERE `inradar_ad_company`.`inradarad_ptr_id` = `inradar_ad`.`id`
 AND `inradar_ad_company`.`corporate_name` LIKE @pattern
 )
 OR EXISTS (
 SELECT `inradar_ad_person`.`inradarad_ptr_id`
 FROM `inradar_ad_person`
 WHERE `inradar_ad_person`.`inradarad_ptr_id` = `inradar_ad`.`id`
 AND `inradar_ad_person`.`name` LIKE @pattern
 )
 OR EXISTS (
 SELECT `inradar_ad_category`.`id`
 FROM `inradar_ad_category`
 WHERE `inradar_ad_category`.`id` = `inradar_ad`.`category_id`
 AND `inradar_ad_category`.`name` LIKE @pattern
 )
 OR EXISTS (
 SELECT `inradar_ad_subcategory`.`id`
 FROM `inradar_ad_subcategory`
 WHERE `inradar_ad_subcategory`.`id` = `inradar_ad`.`category_id`
 AND `inradar_ad_subcategory`.`name` LIKE @pattern
 )
 )
 ORDER BY
 CASE WHEN `basicuser`.`tertiarygroup_id` = givenuser.`tertiarygroup_id` THEN 3
 WHEN `basicuser`.`subgroup_id` = givenuser.`subgroup_id` THEN 2
 WHEN `basicuser`.`group_id` = givenuser.`group_id` THEN 1
 ELSE 0
 END DESC,
 orig.distance;

• Patrick Bassut: orig is not defined yet in the second select, therefore can't be used inside the select. Right? Is there a workaround for this?
• 200_success: You're right. Try Rev 2?
• Patrick Bassut: for some reason, dest can't be used as well? Doesn't make sense
• 200_success: Hmm. It's going to require some reworking.
• Patrick Bassut: Can you help me rework on this, please? Currently call SearchAdsOrderedByGroupThenLocation(-22.7418772, -43.439604, 100.000000, 'a', 0, 100) takes up to 5 seconds. =/