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;ENDHere'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)'
Geographic search
performance;sql;mysql;geospatial;join