optimizing query for slow queries

Question

I have this query in my slowqueries table which essentially is taking about 5-8 seconds to complete the query time:

SELECT s0_.id AS id0, s0_.updated AS updated1, 
 s0_.created AS created2, 
 s0_.deletedAt AS deletedAt3, 
 s0_.checked AS checked4, 
 s0_.mediaid AS mediaid5, 
 s0_.numberoflikes AS numberoflikes6, 
 s0_.numberofdislikes AS numberofdislikes7, 
 s0_.numberofcomments AS numberofcomments8, 
 s0_.createdtimestamp AS createdtimestamp9, 
 s0_.caption AS caption10, 
 s0_.captionOriginal AS captionOriginal11, 
 s0_.price AS price12, 
 s0_.link AS link13, 
 s0_.lowresimageurl AS lowresimageurl14, 
 s0_.highresimageurl AS highresimageurl15, 
 s0_.medresimageurl AS medresimageurl16, 
 s0_.isLocked AS isLocked17, 
 s0_.isShown AS isShown18, 
 s0_.isStyleInspiration AS isStyleInspiration19, 
 s0_.color AS color20, 
 s0_.viewed AS viewed21, 
 s0_.viewed_today AS viewed_today22, 
 s0_.shop_id AS shop_id23 
FROM app_instagram_shop_picture s0_ 
LEFT JOIN app_instagram_shop s1_ 
ON s0_.shop_id = s1_.id AND (s1_.deletedAt IS NULL) 
WHERE 
 (s0_.isShown = 1 AND 
 s0_.isStyleInspiration = 0 AND 
 s1_.id <> 24576 AND 
 s0_.id <> '513385.25' AND 
 s1_.isLocked = 1 AND 
 s0_.isLocked = 0 AND 
 s0_.id >= '513385.25') AND 
 (s0_.deletedAt IS NULL) 
ORDER BY s0_.updated DESC LIMIT 16;

I wonder what is the best way to optimize this query? I have been using percona toolkit and here's the output for this particular query.

# Query 1: 0.01 QPS, 0.03x concurrency, ID 0xFFE8DDA58E282913 at byte 3405997
# This item is included in the report because it matches --limit.
# Scores: V/M = 88.37
# Time range: 2014年11月07日 06:32:19 to 2014年11月11日 16:21:47
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 14 4888
# Exec time 48 13015s 1s 566s 3s 3s 15s 1s
# Lock time 8 664ms 64us 29ms 135us 167us 445us 119us
# Rows sent 11 75.92k 0 16 15.91 15.25 1.17 15.25
# Rows examine 70 7.58G 0 2.54M 1.59M 1.61M 173.18k 1.53M
# Query size 10 5.21M 1.07k 1.09k 1.09k 1.09k 10.50 1.09k
# String:
# Databases AppDirectory
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ #
# Tables
# SHOW TABLE STATUS FROM `AppDirectory` LIKE 'App_instagram_shop_picture'\G
# SHOW CREATE TABLE `AppDirectory`.`App_instagram_shop_picture`\G
# SHOW TABLE STATUS FROM `AppDirectory` LIKE 'App_instagram_shop'\G
# SHOW CREATE TABLE `AppDirectory`.`App_instagram_shop`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT s0_.id AS id0, s0_.updated AS updated1, s0_.created AS created2, s0_.deletedAt AS deletedAt3, s0_.checked AS checked4, s0_.mediaid AS mediaid5, s0_.numberoflikes AS numberoflikes6, s0_.numberofdislikes AS numberofdislikes7, s0_.numberofcomments AS numberofcomments8, s0_.createdtimestamp AS createdtimestamp9, s0_.caption AS caption10, s0_.captionOriginal AS captionOriginal11, s0_.price AS price12, s0_.link AS link13, s0_.lowresimageurl AS lowresimageurl14, s0_.highresimageurl AS highresimageurl15, s0_.medresimageurl AS medresimageurl16, s0_.isLocked AS isLocked17, s0_.isShown AS isShown18, s0_.isStyleInspiration AS isStyleInspiration19, s0_.color AS color20, s0_.viewed AS viewed21, s0_.viewed_today AS viewed_today22, s0_.shop_id AS shop_id23 FROM App_instagram_shop_picture s0_ LEFT JOIN App_instagram_shop s1_ ON s0_.shop_id = s1_.id AND (s1_.deletedAt IS NULL) WHERE (s0_.isShown = 1 AND s0_.isStyleInspiration = 0 AND s1_.id <> 25496 AND s0_.id <> '530649.5' AND s1_.isLocked = 1 AND s0_.isLocked = 0 AND s0_.id >= '530649.5') AND (s0_.deletedAt IS NULL) ORDER BY s0_.updated DESC LIMIT 16\G

Here's the EXPLAIN statement for the query above:

+----+-------------+-------+--------+------------------------------------------------------------------------------+----------------------+---------+-----------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------------------------------------------------+----------------------+---------+-----------------------+------+----------------------------------------------+
| 1 | SIMPLE | s4_ | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | s1_ | ref | PRIMARY,shop_shown_style_deleted_idx,idx_locked_deletedAt,idx_deleted_shopId | idx_locked_deletedAt | 7 | const,const | 37 | Using where; Using index |
| 1 | SIMPLE | s3_ | ref | IDX_9AC480D6EE45BDBF,IDX_9AC480D6683813E0 | IDX_9AC480D6EE45BDBF | 4 | Shopious.s1_.id | 1 | Using where |
| 1 | SIMPLE | s2_ | ref | IDX_2F40D95EEE45BDBF,IDX_2F40D95EBACD6074 | IDX_2F40D95EEE45BDBF | 4 | Shopious.s1_.id | 1 | Using where |
| 1 | SIMPLE | s0_ | eq_ref | PRIMARY | PRIMARY | 4 | Shopious.s2_.style_id | 1 | NULL |
+----+-------------+-------+--------+------------------------------------------------------------------------------+----------------------+---------+-----------------------+------+----------------------------------------------+

SHOW TABLE STATUS

+-----------------------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------------------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| accepted_city | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2015年01月18日 00:49:18 | NULL | NULL | utf8_unicode_ci | NULL | | |
| board | InnoDB | 10 | Compact | 123 | 1864 | 229376 | 0 | 0 | 0 | 128 | 2015年01月18日 00:49:18 | NULL | NULL | utf8_unicode_ci | NULL | | |
| contact_type | InnoDB | 10 | Compact | 6 | 2730 | 16384 | 0 | 0 | 0 | 7 | 2015年01月18日 00:49:18 | NULL | NULL | utf8_unicode_ci | NULL | | |
| guest | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2015年01月18日 00:49:18 | NULL | NULL | utf8_unicode_ci | NULL | | |
| inbox | InnoDB | 10 | Compact | 8 | 2048 | 16384 | 0 | 16384 | 0 | 22 | 2015年01月18日 00:49:18 | NULL | NULL | utf8_unicode_ci | NULL | | |
| inbox_message | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2015年01月18日 00:49:18 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_first_level_category | InnoDB | 10 | Compact | 27 | 2427 | 65536 | 0 | 16384 | 0 | 47 | 2015年01月18日 00:49:18 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_item_viewer | InnoDB | 10 | Compact | 9708750 | 38 | 377307136 | 0 | 395804672 | 5242880 | 13423064 | 2015年01月18日 00:49:18 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_picture_board | InnoDB | 10 | Compact | 1395 | 46 | 65536 | 0 | 98304 | 0 | 1932 | 2015年01月18日 00:52:39 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_picture_category | InnoDB | 10 | Compact | 403122 | 45 | 18366464 | 0 | 31145984 | 5242880 | 1731917 | 2015年01月18日 00:52:39 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_picture_failed_to_load | InnoDB | 10 | Compact | 77687 | 101 | 7880704 | 0 | 2555904 | 4194304 | 192300 | 2015年01月18日 00:52:48 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_picture_style | InnoDB | 10 | Compact | 69623 | 37 | 2637824 | 0 | 5275648 | 4194304 | 156148 | 2015年01月18日 00:52:51 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_picture_tag | InnoDB | 10 | Compact | 14875477 | 34 | 512720896 | 0 | 627834880 | 8388608 | 26998313 | 2015年02月15日 04:19:22 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_prospective_buyer_category | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 49152 | 0 | 1 | 2015年01月18日 00:58:08 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_second_level_category | InnoDB | 10 | Compact | 101 | 1135 | 114688 | 0 | 16384 | 0 | 115 | 2015年01月18日 00:58:08 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_shop | InnoDB | 10 | Compact | 30834 | 459 | 14172160 | 0 | 3407872 | 4194304 | 33870 | 2015年01月18日 00:58:08 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| instagram_shop_category | InnoDB | 10 | Compact | 2213 | 51 | 114688 | 0 | 180224 | 0 | 3352 | 2015年01月18日 00:58:12 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_shop_contact | InnoDB | 10 | Compact | 31616 | 83 | 2637824 | 0 | 1589248 | 4194304 | 31738 | 2015年02月09日 02:12:00 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_shop_following | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2015年01月18日 00:58:14 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_shop_picture | InnoDB | 10 | Compact | 1351675 | 851 | 1150287872 | 0 | 255197184 | 11534336 | 2866818 | 2015年02月15日 02:57:11 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_shop_picture_metadata | InnoDB | 10 | Compact | 54188 | 48 | 2637824 | 0 | 1589248 | 4194304 | 58817 | 2015年02月09日 02:11:54 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_shop_viewer | InnoDB | 10 | Compact | 921529 | 38 | 35192832 | 0 | 47284224 | 5242880 | 1087385 | 2015年01月18日 01:01:58 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_tag | InnoDB | 10 | Compact | 488506 | 130 | 63520768 | 0 | 116604928 | 48234496 | 1562800 | 2015年02月17日 10:15:21 | NULL | NULL | utf8_unicode_ci | NULL | | |
| instagram_top_category | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 0 | 0 | 6 | 2015年01月18日 01:02:29 | NULL | NULL | utf8_unicode_ci | NULL | | |
| item_comment | InnoDB | 10 | Compact | 3833 | 94 | 360448 | 0 | 163840 | 0 | 21520 | 2015年01月18日 01:02:30 | NULL | NULL | utf8_unicode_ci | NULL | | |
| itemdislike | InnoDB | 10 | Compact | 275 | 59 | 16384 | 0 | 32768 | 0 | 1656 | 2015年01月18日 01:02:30 | NULL | NULL | utf8_unicode_ci | NULL | | |
| itemlike | InnoDB | 10 | Compact | 3198 | 46 | 147456 | 0 | 262144 | 0 | 22448 | 2015年01月18日 01:02:30 | NULL | NULL | utf8_unicode_ci | NULL | | |
| location | InnoDB | 10 | Compact | 42542 | 62 | 2637824 | 0 | 1589248 | 4194304 | 50757 | 2015年01月18日 01:02:30 | NULL | NULL | utf8_unicode_ci | NULL | | |
| location_city | InnoDB | 10 | Compact | 5602 | 55 | 311296 | 0 | 147456 | 0 | 5603 | 2015年01月18日 01:02:32 | NULL | NULL | utf8_unicode_ci | NULL | | |
| location_province | InnoDB | 10 | Compact | 32 | 512 | 16384 | 0 | 0 | 0 | 33 | 2015年01月18日 01:02:32 | NULL | NULL | utf8_unicode_ci | NULL | | |
| location_state | InnoDB | 10 | Compact | 466 | 105 | 49152 | 0 | 16384 | 0 | 546 | 2015年01月18日 01:02:32 | NULL | NULL | utf8_unicode_ci | NULL | | |
| message | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2015年01月18日 01:02:32 | NULL | NULL | utf8_unicode_ci | NULL | | |
| message_metadata | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2015年01月18日 01:02:32 | NULL | NULL | utf8_unicode_ci | NULL | | |
| pack_type | InnoDB | 10 | Compact | 4 | 4096 | 16384 | 0 | 0 | 0 | 5 | 2015年01月18日 01:02:32 | NULL | NULL | utf8_unicode_ci | NULL | | |
| payment_method | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 0 | 3 | 2015年01月18日 01:02:33 | NULL | NULL | utf8_unicode_ci | NULL | | |
| picture_renamer | InnoDB | 10 | Compact | 132261 | 43 | 5783552 | 0 | 6324224 | 4194304 | 187620 | 2015年01月18日 01:02:33 | NULL | NULL | utf8_unicode_ci | NULL | | |
| preferred_contact_type | InnoDB | 10 | Compact | 1039 | 63 | 65536 | 0 | 32768 | 0 | 1282 | 2015年01月18日 01:02:36 | NULL | NULL | utf8_unicode_ci | NULL | | |
| price_range | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2015年01月18日 01:02:36 | NULL | NULL | utf8_unicode_ci | NULL | | |
| profile_picture | InnoDB | 10 | Compact | 32880 | 144 | 4734976 | 0 | 507904 | 4194304 | 37148 | 2015年01月18日 01:02:36 | NULL | NULL | utf8_unicode_ci | NULL | | |
| prospective_buyer | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2015年01月18日 01:02:38 | NULL | NULL | utf8_unicode_ci | NULL | | |
| prospective_organic_shop | InnoDB | 10 | Compact | 1840 | 71 | 131072 | 0 | 0 | 0 | 2054 | 2015年01月18日 01:02:38 | NULL | NULL | utf8_unicode_ci | NULL | | |
| prospective_shop | InnoDB | 10 | Compact | 190504 | 173 | 33095680 | 0 | 13156352 | 7340032 | 201721 | 2015年01月18日 01:02:38 | NULL | NULL | utf8_unicode_ci | NULL | | |
| prospective_shop_status | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 0 | 4 | 2015年01月18日 01:02:49 | NULL | NULL | utf8_unicode_ci | NULL | | |
| receipt | InnoDB | 10 | Compact | 5701 | 68 | 393216 | 0 | 458752 | 0 | 6585 | 2015年01月18日 01:02:49 | NULL | NULL | utf8_unicode_ci | NULL | | |
| shipping_cost | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2015年01月18日 01:02:49 | NULL | NULL | utf8_unicode_ci | NULL | | |
| shipping_cost_express | InnoDB | 10 | Compact | 5711 | 80 | 458752 | 0 | 0 | 0 | 5553 | 2015年01月18日 01:02:49 | NULL | NULL | latin1_swedish_ci | NULL | | |
| shipping_info | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2015年01月18日 01:02:50 | NULL | NULL | utf8_unicode_ci | NULL | | |
| source | InnoDB | 10 | Compact | 6 | 2730 | 16384 | 0 | 0 | 0 | 7 | 2015年01月18日 01:02:50 | NULL | NULL | utf8_unicode_ci | NULL | | |
| style | InnoDB | 10 | Compact | 126 | 1690 | 212992 | 0 | 0 | 0 | 143 | 2015年01月18日 01:02:50 | NULL | NULL | utf8_unicode_ci | NULL | | |
| tag | InnoDB | 10 | Compact | 2200 | 52 | 114688 | 0 | 0 | 0 | 2255 | 2015年01月18日 01:02:50 | NULL | NULL | utf8_unicode_ci | NULL | | |
| thread | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2015年01月18日 01:02:51 | NULL | NULL | utf8_unicode_ci | NULL | | |
| thread_metadata | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2015年01月18日 01:02:51 | NULL | NULL | utf8_unicode_ci | NULL | | |
| transaction | InnoDB | 10 | Compact | 6512 | 244 | 1589248 | 0 | 606208 | 4194304 | 8449 | 2015年01月18日 01:02:51 | NULL | NULL | utf8_unicode_ci | NULL | | |
| user | InnoDB | 10 | Compact | 32047 | 344 | 11026432 | 0 | 6258688 | 4194304 | 35599 | 2015年01月18日 01:02:52 | NULL | NULL | utf8_unicode_ci | NULL | | |
| user_follow_user | InnoDB | 10 | Compact | 17 | 963 | 16384 | 0 | 32768 | 0 | NULL | 2015年01月18日 01:02:56 | NULL | NULL | utf8_unicode_ci | NULL | | |
| viewer | InnoDB | 10 | Compact | 360950 | 56 | 20496384 | 0 | 19988480 | 7340032 | 397608 | 2015年01月18日 01:02:56 | NULL | NULL | utf8_unicode_ci | NULL | | |
| transmission | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2015年01月18日 01:03:07 | NULL | NULL | utf8_unicode_ci | NULL | | |
+-----------------------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

SHOW CREATE TABLE style;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table |

+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| style | CREATE TABLE `style` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `style` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `styleTitle` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `styleTitleDescription` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
 `styleDescription` varchar(5120) COLLATE utf8_unicode_ci DEFAULT NULL,
 `coverUrl` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `selected_at_home` tinyint(1) NOT NULL,
 `smallCoverUrl` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `styleFontCustom` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `styleTitleColor` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `created` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=143 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

• Allan S. Hansen: The best way is to look at execution plans and add indexes where needed.
• jynus: @adit If you want us to help, you need to provide the output of everything that is commented at the last portion of pt-query-digest, all the SHOW TABLE STATUS, all the SHOW CREATE TABLE and the EXPLAIN SELECT. If not, we are just guessing. Even that may no be enough.
• adit: @jynus I added the explain statement above, I can't do pt-query-digest since this is on an rds machine

Answer 1

Indexes are usually the best way to increase the speed in any query, but there are other alternatives that might help:

1) Check if the field you are joining the tables are INTEGERS instead of CHAR/VARCHAR. In your case would be s0_.shop_id = s1_.id. Replace them by INTEGERS if possible, that reduces the time to join the table during the query.

2) The field s0_.id seems to be a CHAR/VARCHAR, but looks like you are storing float/double values on it. Change the field type to float/double.

3) Depending on what database you are using (Oracle, Mysql, Postgre, ...) it might have an embedded cache system. Even if it doesn't have, you can use Memcache or Redis.