MySQL - Query performance issues - taking 25+ seconds <p>I've been trying to figure out why this query is taking so long. Before, it was executing in approx 150ms to 200ms but now it's taking 25 seconds or longer. And this was between last night and this mroning. The only thing that's changed is add data to tables.</p> <p>Based on the explain output below and also the sql I've provided, is there anything that stands out that would explain why the query takes so long now?</p> <p>update: Per the comment from halfer, I changed the order by to <code>ORDER BY pins.pin_id</code> and it brought the execution time to 172ms.</p> <p>Also, I wanted to mention that the table pins has 20 million records, boards as 339,000 records, users has 352738 records.</p> <pre><code>+----+--------------------+---------------+-----------------+----------------------------------------------+---------+---------+--------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------------+-----------------+----------------------------------------------+---------+---------+--------------------------------+------+--------------------------+ | 1 | PRIMARY | &lt;derived11&gt; | system | NULL | NULL | NULL | NULL | 1 | | | 1 | PRIMARY | pins | index | user_id,user_id_2,latitude,longitude,lat_lon | vip | 1 | NULL | 646 | Using where | | 1 | PRIMARY | users | eq_ref | PRIMARY | PRIMARY | 8 | skoovy_prd.pins.user_id | 1 | | | 1 | PRIMARY | boards | eq_ref | PRIMARY | PRIMARY | 8 | skoovy_prd.pins.board_id | 1 | | | 1 | PRIMARY | via | eq_ref | PRIMARY | PRIMARY | 8 | skoovy_prd.pins.via | 1 | | | 12 | DEPENDENT SUBQUERY | users | unique_subquery | PRIMARY | PRIMARY | 8 | func | 1 | Using where | | 11 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 10 | DEPENDENT SUBQUERY | users_avatars | ref | pin_id | pin_id | 14 | skoovy_prd.users.user_id,const | 1 | Using where | | 9 | DEPENDENT SUBQUERY | users_avatars | ref | pin_id | pin_id | 14 | skoovy_prd.users.user_id,const | 1 | Using where | | 8 | DEPENDENT SUBQUERY | users_avatars | ref | pin_id | pin_id | 14 | skoovy_prd.users.user_id,const | 1 | Using where | | 7 | DEPENDENT SUBQUERY | pins_images | ref | pin_id | pin_id | 14 | skoovy_prd.pins.pin_id,const | 1 | Using where | | 6 | DEPENDENT SUBQUERY | pins_images | ref | pin_id | pin_id | 14 | skoovy_prd.pins.pin_id,const | 1 | Using where | | 5 | DEPENDENT SUBQUERY | pins_images | ref | pin_id | pin_id | 14 | skoovy_prd.pins.pin_id,const | 1 | Using where | | 4 | DEPENDENT SUBQUERY | pins_images | ref | pin_id | pin_id | 14 | skoovy_prd.pins.pin_id,const | 1 | Using where | | 3 | DEPENDENT SUBQUERY | pins_reports | index | user_id | user_id | 115 | NULL | 1 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | pins_likes | ref | pin_id,pin_id_2,user_id | pin_id | 16 | skoovy_prd.pins.pin_id,const | 1 | Using index | +----+--------------------+---------------+-----------------+----------------------------------------------+---------+---------+--------------------------------+------+--------------------------+ </code></pre> <p>The SQL:</p> <pre><code>SELECT `pins`.`pin_id` AS `pin_pin_id`, `pins`.`check_unique_id` AS `pin_check_unique_id`, `pins`.`category_id` AS `pin_category_id`, `pins`.`board_id` AS `pin_board_id`, `pins`.`user_id` AS `pin_user_id`, `pins`.`date_added` AS `pin_date_added`, `pins`.`date_modified` AS `pin_date_modified`, `pins`.`likes` AS `pin_likes`, `pins`.`comments` AS `pin_comments`, `pins`.`repins` AS `pin_repins`, `pins`.`description` AS `pin_description`, `pins`.`title` AS `pin_title`, `pins`.`image` AS `pin_image`, `pins`.`price` AS `pin_price`, `pins`.`price_currency_code` AS `pin_price_currency_code`, `pins`.`price_type` AS `pin_price_type`, `pins`.`price_from` AS `pin_price_from`, `pins`.`price_to` AS `pin_price_to`, `pins`.`event_type` AS `pin_event_type`, `pins`.`event_start` AS `pin_event_start`, `pins`.`event_end` AS `pin_event_end`, `pins`.`from` AS `pin_from`, `pins`.`from_md5` AS `pin_from_md5`, `pins`.`from_repin` AS `pin_from_repin`, `pins`.`is_video` AS `pin_is_video`, `pins`.`views` AS `pin_views`, `pins`.`latest_comments` AS `pin_latest_comments`, `pins`.`source_id` AS `pin_source_id`, `pins`.`via` AS `pin_via`, `pins`.`repin_from` AS `pin_repin_from`, `pins`.`public` AS `pin_public`, `pins`.`ub_id` AS `pin_ub_id`, `pins`.`total_views` AS `pin_total_views`, `pins`.`delete_request` AS `pin_delete_request`, `pins`.`pinmarklet` AS `pin_pinmarklet`, `pins`.`vip` AS `pin_vip`, `pins`.`store` AS `pin_store`, `pins`.`width` AS `pin_width`, `pins`.`height` AS `pin_height`, `pins`.`ext` AS `pin_ext`, `pins`.`location` AS `pin_location`, `pins`.`latitude` AS `pin_latitude`, `pins`.`longitude` AS `pin_longitude`, IF(pins.price_type != '' AND (pins.price_type = 'CUSTOM_RANGE' AND pins.price_from &lt;&gt; 0.00 AND pins.price_to &lt;&gt; 0.00 AND pins.price_from &lt; pins.price_to) OR (pins.price_from &lt;&gt; 0.00), 1, 0) AS `pin_show_pricing`, IF(pins.price_type = 'CUSTOM_RANGE', CONCAT('$', pins.price_from, '-', '$', pins.price_to), CONCAT('$', pins.price_from)) AS `pin_price_text`, IF((pins.event_start IS NOT NULL || pins.event_end IS NOT NULL) &amp;&amp; (pins.event_start != '0000-00-00 00:00:00' || pins.event_end != '0000-00-00 00:00:00') &amp;&amp; (event_type = 'multiple' || event_type = 'single'), 1, 0) AS `pin_show_event`, IF(pins.event_type = 'multiple', CONCAT(DATE_FORMAT(pins.event_start, '%Y-%m-%d'), ' - ', DATE_FORMAT(pins.event_end, '%Y-%m-%d')), DATE_FORMAT(pins.event_start, '%Y-%m-%d')) AS `pin_event_text`, (SELECT COUNT(like_id) FROM `pins_likes` WHERE (pin_id = pins.pin_id) AND (user_id = 57610) LIMIT 1) AS `pin_is_liked`, (SELECT COUNT(pr_id) FROM `pins_reports` WHERE (pin_id = pins.pin_id) AND (checked = 0) AND (user_id = '57610' OR user_ip = '44021cb8') LIMIT 1) AS `pin_is_reported`, (SELECT CONCAT_WS('|||', image, width, height, original, mime) FROM `pins_images` WHERE (pin_id = pins.pin_id) AND (size = '_A') LIMIT 1) AS `pin_thumb_a`, (SELECT CONCAT_WS('|||', image, width, height, original, mime) FROM `pins_images` WHERE (pin_id = pins.pin_id) AND (size = '_B') LIMIT 1) AS `pin_thumb_b`, (SELECT CONCAT_WS('|||', image, width, height, original, mime) FROM `pins_images` WHERE (pin_id = pins.pin_id) AND (size = '_C') LIMIT 1) AS `pin_thumb_c`, (SELECT CONCAT_WS('|||', image, width, height, original, mime) FROM `pins_images` WHERE (pin_id = pins.pin_id) AND (size = '_D') LIMIT 1) AS `pin_thumb_d`, `users`.`user_id` AS `user_user_id`, `users`.`source` AS `user_source`, `users`.`source_uuid` AS `user_source_uuid`, `users`.`is_business` AS `user_is_business`, `users`.`username` AS `user_username`, `users`.`password` AS `user_password`, `users`.`email` AS `user_email`, `users`.`new_email` AS `user_new_email`, `users`.`new_email_key` AS `user_new_email_key`, `users`.`business_name` AS `user_business_name`, `users`.`business_phone` AS `user_business_phone`, `users`.`firstname` AS `user_firstname`, `users`.`lastname` AS `user_lastname`, `users`.`groups` AS `user_groups`, `users`.`status` AS `user_status`, `users`.`last_action_datetime` AS `user_last_action_datetime`, `users`.`last_login` AS `user_last_login`, `users`.`ip_address` AS `user_ip_address`, `users`.`following` AS `user_following`, `users`.`followers` AS `user_followers`, `users`.`avatar` AS `user_avatar`, `users`.`facebook_id` AS `user_facebook_id`, `users`.`twitter_id` AS `user_twitter_id`, `users`.`twitter_username` AS `user_twitter_username`, `users`.`is_admin` AS `user_is_admin`, `users`.`is_developer` AS `user_is_developer`, `users`.`gender` AS `user_gender`, `users`.`location` AS `user_location`, `users`.`latitude` AS `user_latitude`, `users`.`longitude` AS `user_longitude`, `users`.`website` AS `user_website`, `users`.`date_added` AS `user_date_added`, `users`.`new_password` AS `user_new_password`, `users`.`new_password_key` AS `user_new_password_key`, `users`.`facebook_session` AS `user_facebook_session`, `users`.`boards` AS `user_boards`, `users`.`pins` AS `user_pins`, `users`.`likes` AS `user_likes`, `users`.`latest_pins` AS `user_latest_pins`, `users`.`description` AS `user_description`, `users`.`facebook_connect` AS `user_facebook_connect`, `users`.`facebook_timeline` AS `user_facebook_timeline`, `users`.`twitter_connect` AS `user_twitter_connect`, `users`.`dont_search_index` AS `user_dont_search_index`, `users`.`delete_account` AS `user_delete_account`, `users`.`delete_account_date` AS `user_delete_account_date`, `users`.`groups_pin_email` AS `user_groups_pin_email`, `users`.`comments_email` AS `user_comments_email`, `users`.`likes_email` AS `user_likes_email`, `users`.`repins_email` AS `user_repins_email`, `users`.`follows_email` AS `user_follows_email`, `users`.`email_interval` AS `user_email_interval`, `users`.`digest_email` AS `user_digest_email`, `users`.`news_email` AS `user_news_email`, `users`.`first_login` AS `user_first_login`, `users`.`store` AS `user_store`, `users`.`width` AS `user_width`, `users`.`height` AS `user_height`, `users`.`instagram_connect` AS `user_instagram_connect`, `users`.`instagram_profile_id` AS `user_instagram_profile_id`, `users`.`instagram_token` AS `user_instagram_token`, `users`.`enable_follow` AS `user_enable_follow`, `users`.`public` AS `user_public`, users.username AS `user_fullname`, (SELECT CONCAT_WS('|||', image, width, height, original, mime) FROM `users_avatars` WHERE (user_id = users.user_id) AND (size = '_A') LIMIT 1) AS `user_avatar_a`, (SELECT CONCAT_WS('|||', image, width, height, original, mime) FROM `users_avatars` WHERE (user_id = users.user_id) AND (size = '_B') LIMIT 1) AS `user_avatar_b`, (SELECT CONCAT_WS('|||', image, width, height, original, mime) FROM `users_avatars` WHERE (user_id = users.user_id) AND (size = '_C') LIMIT 1) AS `user_avatar_c`, `boards`.`board_id` AS `board_board_id`, `boards`.`category_id` AS `board_category_id`, `boards`.`user_id` AS `board_user_id`, `boards`.`image` AS `board_image`, `boards`.`date_added` AS `board_date_added`, `boards`.`date_modified` AS `board_date_modified`, `boards`.`sort_order` AS `board_sort_order`, `boards`.`title` AS `board_title`, `boards`.`description` AS `board_description`, `boards`.`followers` AS `board_followers`, `boards`.`pins` AS `board_pins`, `boards`.`public` AS `board_public`, `boards`.`latest_pins` AS `board_latest_pins`, `boards`.`cover` AS `board_cover`, `boards`.`delete_request` AS `board_delete_request`, `boards`.`views` AS `board_views`, `boards`.`total_views` AS `board_total_views`, `via`.`user_id` AS `via_user_id`, `via`.`source` AS `via_source`, `via`.`source_uuid` AS `via_source_uuid`, `via`.`is_business` AS `via_is_business`, `via`.`username` AS `via_username`, `via`.`password` AS `via_password`, `via`.`email` AS `via_email`, `via`.`new_email` AS `via_new_email`, `via`.`new_email_key` AS `via_new_email_key`, `via`.`business_name` AS `via_business_name`, `via`.`business_phone` AS `via_business_phone`, `via`.`firstname` AS `via_firstname`, `via`.`lastname` AS `via_lastname`, `via`.`groups` AS `via_groups`, `via`.`status` AS `via_status`, `via`.`last_action_datetime` AS `via_last_action_datetime`, `via`.`last_login` AS `via_last_login`, `via`.`ip_address` AS `via_ip_address`, `via`.`following` AS `via_following`, `via`.`followers` AS `via_followers`, `via`.`avatar` AS `via_avatar`, `via`.`facebook_id` AS `via_facebook_id`, `via`.`twitter_id` AS `via_twitter_id`, `via`.`twitter_username` AS `via_twitter_username`, `via`.`is_admin` AS `via_is_admin`, `via`.`is_developer` AS `via_is_developer`, `via`.`gender` AS `via_gender`, `via`.`location` AS `via_location`, `via`.`latitude` AS `via_latitude`, `via`.`longitude` AS `via_longitude`, `via`.`website` AS `via_website`, `via`.`date_added` AS `via_date_added`, `via`.`new_password` AS `via_new_password`, `via`.`new_password_key` AS `via_new_password_key`, `via`.`facebook_session` AS `via_facebook_session`, `via`.`boards` AS `via_boards`, `via`.`pins` AS `via_pins`, `via`.`likes` AS `via_likes`, `via`.`latest_pins` AS `via_latest_pins`, `via`.`description` AS `via_description`, `via`.`facebook_connect` AS `via_facebook_connect`, `via`.`facebook_timeline` AS `via_facebook_timeline`, `via`.`twitter_connect` AS `via_twitter_connect`, `via`.`dont_search_index` AS `via_dont_search_index`, `via`.`delete_account` AS `via_delete_account`, `via`.`delete_account_date` AS `via_delete_account_date`, `via`.`groups_pin_email` AS `via_groups_pin_email`, `via`.`comments_email` AS `via_comments_email`, `via`.`likes_email` AS `via_likes_email`, `via`.`repins_email` AS `via_repins_email`, `via`.`follows_email` AS `via_follows_email`, `via`.`email_interval` AS `via_email_interval`, `via`.`digest_email` AS `via_digest_email`, `via`.`news_email` AS `via_news_email`, `via`.`first_login` AS `via_first_login`, `via`.`store` AS `via_store`, `via`.`width` AS `via_width`, `via`.`height` AS `via_height`, `via`.`instagram_connect` AS `via_instagram_connect`, `via`.`instagram_profile_id` AS `via_instagram_profile_id`, `via`.`instagram_token` AS `via_instagram_token`, `via`.`enable_follow` AS `via_enable_follow`, `via`.`public` AS `via_public`, via.username AS `via_fullname`, `row` . *, @curRow:=@curRow + 1 AS `pin_pin_row` FROM `pins` LEFT JOIN `users` ON pins.user_id = users.user_id LEFT JOIN `boards` ON pins.board_id = boards.board_id LEFT JOIN `users` AS `via` ON pins.via = via.user_id INNER JOIN (SELECT @curRow:=- 1) AS `row` WHERE (pins.user_id = 57610 OR IF(pins.user_id = 57610, 1, pins.public) = 1) AND (pins.latitude &lt;= 40.78666477795 AND pins.latitude &gt;= 40.64193322205 AND pins.longitude &lt;= - 73.910342436525 AND pins.longitude &gt;= - 74.101288363475) AND (pins.user_id = 57610 OR pins.user_id IN (SELECT user_id FROM users WHERE public = 1)) ORDER BY pins.vip DESC , pins.pin_id DESC LIMIT 30 </code></pre>