# Query 1: 0.35 QPS, 3.23x concurrency, ID 0x4E3DA202FE28609F at byte 94967961
# This item is included in the report because it matches --limit.
# Scores: V/M = 38.86
# Time range: 2015-11-12 02:39:35 to 16:06:20
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   16809
# Exec time     53 156547s    33us    181s      9s     47s     19s   253ms
# Lock time     44  35122s       0     87s      2s     16s      8s   167us
# Rows sent     38 857.89k       0     101   52.26   49.17   12.04   49.17
# Rows examine  43   2.11G       0   1.29M 131.72k   1.09M 315.84k   2.76k
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent    43 599.71M       0 379.10k  36.53k  59.57k  12.55k  33.17k
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables    33  29.69k       0       3    2.09    2.90    0.35    1.96
# Tmp disk tbl  54  29.69k       0       3    2.09    2.90    0.35    1.96
# Tmp tbl size  38  22.36G       0  62.77M   1.57M   4.93M   7.25M  34.83k
# Query size    23  12.54M     780     786  782.41  755.64       0  755.64
# Boolean:
# Filesort      99% yes,   0% no
# Full scan     99% yes,   0% no
# QC Hit         0% yes,  99% no
# Tmp table     99% yes,   0% no
# Tmp table on  99% yes,   0% no
# String:
# Databases    test1
# Hosts        127.0.0.1
# Last errno   0 (16681/99%), 1160 (72/0%), 1030 (35/0%)... 2 more
# Users        test1
# Query_time distribution
#   1us
#  10us  #
# 100us  #
#   1ms  #
#  10ms  ################################################################
# 100ms  ###########################################################
#    1s  #######################
#  10s+  ####################################################
# Tables
#    SHOW TABLE STATUS FROM `test1` LIKE 'posts'\G
#    SHOW CREATE TABLE `test1`.`posts`\G
#    SHOW TABLE STATUS FROM `test1` LIKE 'niche_posts'\G
#    SHOW CREATE TABLE `test1`.`niche_posts`\G
#    SHOW TABLE STATUS FROM `test1` LIKE 'images'\G
#    SHOW CREATE TABLE `test1`.`images`\G
#    SHOW TABLE STATUS FROM `test1` LIKE 'videos'\G
#    SHOW CREATE TABLE `test1`.`videos`\G
# EXPLAIN /*!50100 PARTITIONS*/
select  p1.*, i.id as iid, i.image_name AS iname, v.id as vid, v.video_name as vname, v.cover_name as vcover from (
                SELECT
                    p.id as pid, p.title as ptitle, p.location as plocation, p.map as pmap, p.age as page,
                    p.createdon as pcreatedon, p.phone as phone, p.sponsor as sponsored, p.body as pbody

                FROM  posts p
                INNER JOIN niche_posts np ON np.posts_id = p.id AND np.niche_id = 19
                WHERE p.city_id = 7  AND p.show = 1
                    ORDER  BY p.createdon DESC LIMIT 51 OFFSET 150) as p1
                LEFT JOIN images i ON  i.posts_id = p1.pid
                LEFT JOIN videos v ON  v.posts_id = p1.pid  GROUP  BY p1.pid  ORDER  BY p1.pcreatedon DESC\G

+----+-------------+------------+------------+--------+-----------------------------------+-----------------+---------+----------------------------------+--------+---------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                     | key             | key_len | ref                              | rows   | Extra                           |
+----+-------------+------------+------------+--------+-----------------------------------+-----------------+---------+----------------------------------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                              | NULL            | NULL    | NULL                             |    201 | Using temporary; Using filesort |
|  1 | PRIMARY     | i          | NULL       | ref    | posts_id                          | posts_id        | 4       | p1.pid                           |      6 | NULL                            |
|  1 | PRIMARY     | v          | NULL       | ref    | videos_posts_id                   | videos_posts_id | 4       | p1.pid                           |      1 | NULL                            |
|  2 | DERIVED     | np         | NULL       | ref    | PRIMARY,niche_id,posts_id         | niche_id        | 1       | const                            | 622786 | Using temporary; Using filesort |
|  2 | DERIVED     | p          | NULL       | eq_ref | PRIMARY,city_id_source_createdonn | PRIMARY         | 4       | test1.np.posts_id                |      1 | Using where                     |
+----+-------------+------------+------------+--------+-----------------------------------+-----------------+---------+----------------------------------+--------+---------------------------------+
5 rows in set (0.01 sec)