The following query (which I didn't write) does a search based on distance calculations in a 50,000-record table. The first time I run it (in phpMyAdmin), it runs in under 0.25 seconds. If I immediately run it again, it takes over 30 seconds. I tried adding SQL_NO_CACHE but it made no difference. Since in production the same query could be run multiple times in short order, this is a major concern.
Note that if the user selects additional criteria (keywords) on the search page, which require joined tables in which string searches are done, the problem goes away; I'm assuming that the text searches are done first, leaving fewer distance calculations. (This is why there is a GROUP BY on the primary key; these extended searches can produce multiple instances of the PK.)
Also note that on my local test system, the query runs in under 0.02 seconds no matter how many times I rerun it.
Here's the query (a sample generated from the actual search page):
SELECT
`cc6177_clients`.*,
ACOS(SIN(RADIANS(`b1e39c_client_lat`)) * SIN(RADIANS(51.0486151)) + COS(RADIANS(`b1e39c_client_lat`)) * COS(RADIANS(51.0486151)) * COS(RADIANS(`b1e39c_client_long`) - RADIANS(- 114.0708459))) * 3964
AS `distance`
FROM
`cc6177_clients`
WHERE
(b1e39c_client_status = '1'
AND b1e39c_client_profile_status = '1'
AND b1e39c_client_type = 'provider')
GROUP BY `b1e39c_client_id`
HAVING (distance <= 50)
ORDER BY `b1e39c_client_company_name` ASC
LIMIT 8
Here's the EXPLAIN output:
1 SIMPLE cc6177_clients ref PRIMARY,client_email,client_company_name,main_search main_search 3 const,const,const 26564 Using index condition; Using where; Using filesort
And here's the CREATE TABLE:
CREATE TABLE `cc6177_clients` (
`b1e39c_client_id` int(11) NOT NULL AUTO_INCREMENT,
`b1e39c_client_type` enum('client','provider') COLLATE utf8_unicode_ci NOT NULL,
`b1e39c_client_login_type` enum('normal','social') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'normal',
`b1e39c_client_oauth_provider` enum('facebook','gplus') COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_oauth_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_access_token` text COLLATE utf8_unicode_ci,
`b1e39c_client_referrer_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_nickname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`b1e39c_client_first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_picture` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_country_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_contact_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_lat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_long` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_postal_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '0=>Not Active,1=>Active',
`b1e39c_client_activation_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'It is used for account activation or reset password request key',
`b1e39c_client_verified` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT 'It is used to verify client email address(0=>not Verified,1=>Verified)',
`b1e39c_client_registered_on` datetime NOT NULL,
`b1e39c_client_login_failed_count` int(2) NOT NULL,
`b1e39c_client_login_failed_time` datetime DEFAULT NULL,
`b1e39c_client_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_last_login` datetime DEFAULT NULL,
`b1e39c_client_view_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL,
`b1e39c_client_delete_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '0=>client deleted, 1=> client active',
`b1e39c_client_verify_email_link_exp` datetime DEFAULT NULL,
`b1e39c_client_company_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_website_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_company_logo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Provider Company Logo',
`b1e39c_client_desc` text COLLATE utf8_unicode_ci,
`b1e39c_client_category_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`b1e39c_client_sub_category_id` int(11) DEFAULT NULL,
`b1e39c_client_claim_option` enum('0','1','2') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '0=>default,1=>User Added By Admin,2=>claim then make main provider',
`b1e39c_client_membership_id` int(11) DEFAULT NULL COMMENT 'client current membership table unique id',
`b1e39c_client_cur_membership_id` int(11) DEFAULT NULL COMMENT 'client currency membership',
`b1e39c_client_membership_type` enum('free','paid') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'client membership type',
`b1e39c_client_profile_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`b1e39c_client_profile_cover` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`b1e39c_client_id`),
UNIQUE KEY `client_email` (`b1e39c_client_email`),
KEY `client_company_name` (`b1e39c_client_company_name`),
KEY `main_search` (`b1e39c_client_status`,`b1e39c_client_profile_status`,`b1e39c_client_type`)
) ENGINE=InnoDB AUTO_INCREMENT=55931 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Don't use VARCHAR(255) everywhere; use reasonable limits. (This will help with the performance of the temp tables that are currently needed.
Use a "bounding box" in the WHERE clause, plus INDEX(lat), INDEX(long). (See below.)
Don't clutter the SQL with a constant prefix (b1e39c_client_). (A Human thing.)
Get rid of GROUP BY; it adds nothing to this query other than slowing it down. (This eliminates an unnecessary pass over the data.)
None of those can explain why the first run was significantly faster than the next run.
A "bounding box" is something like
AND lat BETWEEN .. AND ..
AND long BETWEEN .. AND ..
with the values filled in to be 50 units away from the source lat/long. (Note that you need to divide by cosd(lat) for the long tests.)
This will speed up the query by an order of magnitude, thereby making it so fast that your original question becomes moot.
Without the bounding box, this might help:
INDEX(status, profile_status, type, -- in any order
company_name) -- last
If you want to pursue the original question, see if you can get EXPLAIN SELECT ... to show two different outputs.