11
\$\begingroup\$

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)'
\$\endgroup\$
6
\$\begingroup\$

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;
\$\endgroup\$
  • \$\begingroup\$ When encapsulating the part where the distance calculation occurs inside a function it gave me a .2 sec latency more. Weird, right? \$\endgroup\$ – Patrick Bassut Jun 1 '14 at 22:38
  • \$\begingroup\$ 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. \$\endgroup\$ – David Harkness Jun 1 '14 at 23:53
5
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ added the explain output \$\endgroup\$ – Patrick Bassut Jun 1 '14 at 19:02
  • \$\begingroup\$ 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? \$\endgroup\$ – Patrick Bassut Jun 1 '14 at 22:34
  • \$\begingroup\$ I take back what I said about the query getting optimized. It did a little, not much. 12 secs. \$\endgroup\$ – Patrick Bassut Jun 8 '14 at 16:09
4
\$\begingroup\$

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;
\$\endgroup\$
  • \$\begingroup\$ orig is not defined yet in the second select, therefore can't be used inside the select. Right? Is there a workaround for this? \$\endgroup\$ – Patrick Bassut Jun 18 '14 at 20:37
  • \$\begingroup\$ You're right. Try Rev 2? \$\endgroup\$ – 200_success Jun 18 '14 at 20:46
  • \$\begingroup\$ for some reason, dest can't be used as well? Doesn't make sense \$\endgroup\$ – Patrick Bassut Jun 18 '14 at 20:49
  • \$\begingroup\$ Hmm. It's going to require some reworking. \$\endgroup\$ – 200_success Jun 18 '14 at 21:19
  • \$\begingroup\$ 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. =/ \$\endgroup\$ – Patrick Bassut Jan 27 '15 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.