SELECT ads.*
       users.phone, users.firstName AS first_name, users.email AS user_email,
       agencies.name AS agency_name, agencies.phone AS agency_phone,
       agencies.email AS agency_email, agencies.site AS agency_site, 
       IFNULL(images.ad_id, UUID()) as unq_ancestor,
       GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
LEFT JOIN images
	 ON (images.ad_id = ads.id)
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
WHERE ads.publish_status = 4
	 AND ads.object_type IN (1, 2, 3, 4)
GROUP BY unq_ancestor

innodb_version          5.6.19
protocol_version        10
version                 5.6.19-0ubuntu0.14.04.1
version_compile_machine x86_64
version_compile_os      debian-linux-gnu

CREATE TABLE `ads` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `external_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 `external_url` varchar(256) NOT NULL DEFAULT '',
 `status` tinyint(4) NOT NULL,
 `publish_status` tinyint(4) NOT NULL,
 `moderation_comment` varchar(512) NOT NULL DEFAULT '',
 `user_id` mediumint(8) unsigned NOT NULL,
 `boards_to_publish` varchar(255) NOT NULL DEFAULT '',
 `created` datetime NOT NULL,
 `touch` datetime NOT NULL,
 `title` varchar(512) NOT NULL DEFAULT '',
 `offer_type` tinyint(4) NOT NULL,
 `object_type` tinyint(4) NOT NULL,
 `geo_coordinates` varchar(64) NOT NULL DEFAULT '',
 `geo_address` varchar(512) NOT NULL DEFAULT '',
 `geo_country` varchar(255) NOT NULL DEFAULT '',
 `geo_obl` varchar(256) NOT NULL DEFAULT '',
 `geo_raion` varchar(256) NOT NULL DEFAULT '',
 `geo_city_raion` varchar(256) NOT NULL DEFAULT '',
 `geo_place` varchar(256) NOT NULL DEFAULT '',
 `geo_street` varchar(256) NOT NULL DEFAULT '',
 `geo_house` varchar(64) NOT NULL DEFAULT '',
 `geo_metro` varchar(128) NOT NULL DEFAULT '',
 `rent_type` tinyint(4) NOT NULL DEFAULT '0',
 `commission_agent` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `is_mortgage_possible` tinyint(4) NOT NULL DEFAULT '0',
 `is_rent_need_deposit` tinyint(4) NOT NULL DEFAULT '0',
 `price` int(10) unsigned NOT NULL DEFAULT '0',
 `is_new_building` tinyint(4) NOT NULL DEFAULT '0',
 `ready_date` date NOT NULL,
 `year_build` smallint(5) unsigned NOT NULL DEFAULT '0',
 `building_type` tinyint(4) NOT NULL DEFAULT '0',
 `commercial_type` tinyint(4) NOT NULL DEFAULT '0',
 `land_type` tinyint(4) NOT NULL DEFAULT '0',
 `garage_type` tinyint(4) NOT NULL DEFAULT '0',
 `wall_material` tinyint(4) NOT NULL DEFAULT '0',
 `house_type` tinyint(4) NOT NULL DEFAULT '0',
 `series_building` varchar(128) NOT NULL DEFAULT '',
 `floor` tinyint(4) NOT NULL DEFAULT '0',
 `floor_total` tinyint(4) NOT NULL DEFAULT '0',
 `room_total` smallint(5) unsigned NOT NULL DEFAULT '0',
 `room_count_for_sale_rent` tinyint(4) NOT NULL DEFAULT '0',
 `square_total` float NOT NULL DEFAULT '0',
 `square_living` float NOT NULL DEFAULT '0',
 `square_kitchen` float NOT NULL DEFAULT '0',
 `square_room` float NOT NULL DEFAULT '0',
 `square_land` float NOT NULL DEFAULT '0',
 `square_living_rooms` varchar(128) NOT NULL DEFAULT '',
 `layout` tinyint(4) NOT NULL DEFAULT '0',
 `ceiling_height` float NOT NULL DEFAULT '0',
 `wc_type` tinyint(4) NOT NULL DEFAULT '0',
 `repair_type` tinyint(4) NOT NULL DEFAULT '0',
 `floor_type` tinyint(4) NOT NULL DEFAULT '0',
 `overlap_type` tinyint(4) NOT NULL DEFAULT '0',
 `window_type` tinyint(4) NOT NULL DEFAULT '0',
 `view_type` tinyint(4) NOT NULL DEFAULT '0',
 `parking_type` tinyint(4) NOT NULL DEFAULT '0',
 `balcony_type` tinyint(4) NOT NULL DEFAULT '0',
 `toilet_type` tinyint(4) NOT NULL DEFAULT '0',
 `is_passenger_lift` tinyint(4) NOT NULL DEFAULT '0',
 `is_service_lift` tinyint(4) NOT NULL DEFAULT '0',
 `is_television` tinyint(4) NOT NULL DEFAULT '0',
 `is_rubbish_chute` tinyint(4) NOT NULL DEFAULT '0',
 `is_furniture` tinyint(4) NOT NULL DEFAULT '0',
 `is_air_conditioning` tinyint(4) NOT NULL DEFAULT '0',
 `is_household_appliances` tinyint(4) NOT NULL DEFAULT '0',
 `is_phone` tinyint(4) NOT NULL DEFAULT '0',
 `is_internet` tinyint(4) NOT NULL DEFAULT '0',
 `is_heating` tinyint(4) NOT NULL DEFAULT '0',
 `is_gas` tinyint(4) NOT NULL DEFAULT '0',
 `is_electricity` tinyint(4) NOT NULL DEFAULT '0',
 `is_water` tinyint(4) NOT NULL DEFAULT '0',
 `is_sewerage` tinyint(4) NOT NULL DEFAULT '0',
 `is_permanent_residence` tinyint(4) NOT NULL DEFAULT '0',
 `is_sauna` tinyint(4) NOT NULL DEFAULT '0',
 `is_garage` tinyint(4) NOT NULL DEFAULT '0',
 `description` text NOT NULL,
 `description_ext` text NOT NULL,
 `description_ext_m` text NOT NULL,
 `description_ext_o` text NOT NULL,
 `description_ext_r` text NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `external_id` (`external_id`),
 KEY `status` (`status`),
 KEY `publish_status` (`publish_status`),
 KEY `object_type` (`object_type`)
) ENGINE=InnoDB AUTO_INCREMENT=37324 DEFAULT CHARSET=utf8

CREATE TABLE `users` (
 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(512) NOT NULL DEFAULT '',
 `phone` varchar(15) NOT NULL,
 `firstName` varchar(100) NOT NULL,
 `surName` varchar(100) NOT NULL DEFAULT '',
 `lastName` varchar(100) NOT NULL DEFAULT '',
 `password` varchar(512) NOT NULL,
 `status` int(11) NOT NULL DEFAULT '1',
 `total` float NOT NULL DEFAULT '0',
 `partner_code` varchar(32) NOT NULL DEFAULT '',
 `partner_id` bigint(20) NOT NULL DEFAULT '0',
 `partner_total` float NOT NULL DEFAULT '0',
 `partner_wallet_type` varchar(64) NOT NULL DEFAULT '',
 `partner_wallet_number` varchar(128) NOT NULL DEFAULT '',
 `sms_daily_counter` int(11) NOT NULL DEFAULT '0',
 `new_user_mail_wizard_state` int(11) NOT NULL DEFAULT '0',
 `trial` int(11) NOT NULL DEFAULT '1',
 `user_type` int(11) NOT NULL DEFAULT '1',
 `created` datetime NOT NULL,
 `last_visit` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `partner_code` (`partner_code`),
 KEY `user_type` (`user_type`)
) ENGINE=InnoDB AUTO_INCREMENT=2099 DEFAULT CHARSET=utf8

CREATE TABLE `agencies` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(128) NOT NULL,
 `phone` varchar(128) NOT NULL DEFAULT '',
 `billing_type` int(11) NOT NULL,
 `site` varchar(512) NOT NULL DEFAULT '',
 `address` varchar(4096) NOT NULL DEFAULT '',
 `email` varchar(128) NOT NULL DEFAULT '',
 `about` varchar(5000) NOT NULL DEFAULT '',
 `logo` varchar(512) NOT NULL,
 `total` float NOT NULL,
 `city_phone_code` varchar(16) NOT NULL DEFAULT '',
 `xml_feed_url` varchar(512) NOT NULL DEFAULT '',
 `xml_feed_format` int(11) NOT NULL DEFAULT '0',
 `xml_publish_to` varchar(512) NOT NULL DEFAULT '',
 `created` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=198 DEFAULT CHARSET=utf8

CREATE TABLE `agencies_users` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` mediumint(8) unsigned NOT NULL,
 `agency_id` smallint(5) unsigned NOT NULL,
 `role` tinyint(4) NOT NULL,
 `touch` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `agency_id` (`agency_id`)
) ENGINE=InnoDB AUTO_INCREMENT=727 DEFAULT CHARSET=utf8

CREATE TABLE `images` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(512) NOT NULL,
 `ext` varchar(128) NOT NULL,
 `meta` varchar(128) NOT NULL,
 `gen_name` varchar(512) NOT NULL,
 `size` smallint(5) unsigned NOT NULL,
 `ad_id` int(10) unsigned NOT NULL DEFAULT '0',
 `url` varchar(1024) NOT NULL DEFAULT '',
 `etag` varchar(128) DEFAULT '',
 `created` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ad_id` (`ad_id`)
) ENGINE=InnoDB AUTO_INCREMENT=125612 DEFAULT CHARSET=utf8

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1
#bind-address            = 0.0.0.0

key_buffer              = 8M
max_allowed_packet      = 8M
thread_stack            = 128K
thread_cache_size       = 1
myisam-recover         = BACKUP
max_connections        = 100
max_user_connections   = 75

#table_cache            = 64
#thread_concurrency     = 10

query_cache_limit       = 256K
query_cache_size        = 2M

log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 8M

!includedir /etc/mysql/conf.d/

SELECT SQL_NO_CACHE *
FROM ads
WHERE ads.publish_status = 4

0.0012 seconds
0.0032 seconds
0.0013 seconds

SELECT SQL_NO_CACHE ads.*, users.*
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
WHERE ads.publish_status = 4

0.0024 seconds.
0.0017 seconds
0.0019 seconds

SELECT SQL_NO_CACHE ads.*, users.*, agencies.*
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
WHERE ads.publish_status = 4

0.0025 seconds
0.0024 seconds
0.0027 seconds

SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
	   IFNULL(images.ad_id, UUID()) as unq_ancestor,
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY unq_ancestor

7.4967 seconds
8.3472 seconds
6.7859 seconds

[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.6.19-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 84K (Tables: 12)
[--] Data in InnoDB tables: 102M (Tables: 89)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 9

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 26d 21h 7m 8s (26M q [11.261 qps], 2M conn, TX: 329B, RX: 5B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 170.0M global + 1.0M per thread (100 max threads)
[OK] Maximum possible memory usage: 270.0M (13% of installed RAM)
[OK] Slow queries: 0% (43/26M)
[OK] Highest usage of available connections: 14% (14/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/211.0K
[OK] Key buffer hit rate: 100.0% (855K cached / 6 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 5% (30K temp sorts / 566K sorts)
[!!] Joins performed without indexes: 32311
[OK] Temporary tables created on disk: 5% (146K on disk / 2M total)
[OK] Thread cache hit rate: 86% (317K created / 2M connections)
[!!] Table cache hit rate: 0% (279 open / 67K opened)
[OK] Open file limit used: 1% (70/5K)
[OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
[OK] InnoDB buffer pool / data size: 128.0M/102.3M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64:  http://bit.ly/1mi7c4C 
Variables to adjust:
    query_cache_type (=1)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    table_open_cache (> 2000)

query_cache_type    = 1
join_buffer_size    = 256K
table_open_cache    = 2000

8.0584 seconds
6.3425 seconds
6.4612 seconds

[OK] Query cache efficiency: 25.0% (254 cached / 1K selects)
[!!] Sorts requiring temporary tables: 89% (227 temp sorts / 253 sorts)
Variables to adjust:
    sort_buffer_size (> 256K)
    read_rnd_buffer_size (> 256K)

sort_buffer_size    = 256K
read_rnd_buffer_size    = 256K

6.7029 seconds
7.3590 seconds
7.6332 seconds

KEY `publish_status_object_type` (`publish_status`,`object_type`)

4.5921 second
3.1965 seconds
4.7697 seconds

4.6068 seconds
4.9824 seconds
3.9361 seconds

SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
           images.id,
	    IFNULL(images.ad_id, UUID()) as unq_ancestor, 
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY unq_ancestor

2.1628 seconds
1.8497 seconds
1.8589 seconds

SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
           images.id,
	    IFNULL(images.ad_id, UUID()) as unq_ancestor, 
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4

1.2300 seconds
1.2527 seconds
1.2593 seconds

SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
           images.id,
	    IFNULL(images.ad_id, UUID()) as unq_ancestor
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY unq_ancestor

2.0149 seconds
2.0128 seconds
1.9645 seconds

SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
       images.id,
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY images.id

1.7581 seconds
1.7163 seconds
1.6948 seconds

SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
       images.id
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4

0.0023 seconds
0.0033 seconds
0.0026 seconds

SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
           images.id,
	    IFNULL(images.ad_id, RAND()) as unq_ancestor, 
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY unq_ancestor

1.6764 seconds
1.6823 seconds
1.6610 seconds

0.0041
0.0052
0.0044

0.0061
0.0061
0.0058

0.0081
0.0085
0.0084

0.0267
0.0259
0.0266

0.0642
0.0556
0.0572

0.2740
0.2153
0.2159

0.4515
0.4250
0.4205

1.5728
1.4660
1.4847

select SQL_NO_CACHE
       i.ad_id,
       count(1) cnt
from images i
group by i.ad_id

0.0012 seconds
0.0006 seconds
0.0006 seconds

[src]select SQL_NO_CACHE
       i.ad_id,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from images i
group by i.ad_id

0.0018 seconds
0.0016 seconds
0.0017 seconds

select
       a.*, 
       i.ad_id,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from 
      ads a
      JOIN images i on a.id=i.ad_id
group by i.ad_id

0.0030 seconds
0.0003 seconds
0.0003 seconds

select
       a.*, 
       i.ad_id,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from 
      ads a
      JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by i.ad_id

1.0132 seconds
0.0003 seconds
0.0004 seconds

select
       a.*, 
       i.ad_id,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from 
      ads a
      LEFT JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by a.id

0.0021 seconds
0.0004 seconds
0.0003 seconds

select
       a.*, 
       IFNULL(i.ad_id, UUID()) as unq_ancestor,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from 
      ads a
      LEFT JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by unq_ancestor

1.1988 seconds
1.1484 seconds
1.1562 seconds

[OK] Maximum possible memory usage: 270.0M (13% of installed RAM)
[OK] Slow queries: 0% (1/1M)
[OK] Highest usage of available connections: 7% (7/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/211.0K
[OK] Key buffer hit rate: 100.0% (3M cached / 2 reads)
[OK] Query cache efficiency: 20.7% (142K cached / 686K selects)
[!!] Query cache prunes per day: 241503
[!!] Sorts requiring temporary tables: 52% (8K temp sorts / 16K sorts)
[OK] Temporary tables created on disk: 9% (9K on disk / 100K total)
[OK] Thread cache hit rate: 85% (12K created / 89K connections)
[!!] Table cache hit rate: 12% (233 open / 1K opened)
[OK] Open file limit used: 1% (70/5K)
[OK] Table locks acquired immediately: 100% (589K immediate / 589K locks)
[OK] InnoDB buffer pool / data size: 128.0M/104.8M
[OK] InnoDB log waits: 0