I'm trying to optimize a specific query to get fastest time possible. First thing that came to mind was to try Percona 5.6 on a different/better hardware:
Hardware(both have same raid controller/cache/battery):
MySQL SERVER: 4x300GB 10K SAS - RAID10 ( 8 cpu cores )
Percona SERVER: 2x100GB Kingston SSD E100 - RAID1 ( 24 cpu cores )
On MySQL 5.5.33 this query executes in average 15sec.
On Percona 5.6.14 this query executes in average 10sec.
SELECT
a.*,
d.cheap,
d.name AS delivery_name,
cu.name AS cuisine,
IFNULL(a.sec_cuis, d.sec_cuis) AS sec_cuis,
d.ticket,
IF(
TIMESTAMPDIFF(SECOND, a.online, NOW()) <= 120,
1,
0
) AS online,
MAX(
IF(
(
ts.time_from <= CURTIME()
OR (
ts.time_to >= CURTIME()
AND ts.time_to < ts.time_from
)
)
AND (
ts.time_to >= CURTIME()
OR (
ts.time_to < ts.time_from
AND ts.time_from <= CURTIME()
)
)
OR (
ts.time_to = ts.time_from
AND ts.time_to IS NOT NULL
),
1,
0
)
) AS OPEN,
COUNT(DISTINCT re.id) AS reviews_comm
FROM
cats AS c
JOIN time_schedule AS ts
JOIN accounts AS a
JOIN deliveries AS d
JOIN cuisines AS cu
JOIN cuisines2cats AS cc
JOIN products AS p
JOIN specs AS s
LEFT JOIN reviews AS re ON re.account_id = a.id AND re.approved = '1' AND re.active = '1'
WHERE ts.account_id = a.id
AND ts.day = 'tue'
AND a.active = '1'
AND (
a.delivery_type = 1
OR a.delivery_type = '2'
)
AND d.id = a.delivery_id
AND (
d.def_cuis = cu.id
OR a.def_cuis = cu.id
)
AND d.active = '1'
AND cc.cat_id = c.id
AND cc.cuisine_id = '3'
AND a.delivery_id = c.delivery_id
AND p.cat_id = c.id
AND s.product_id = p.id
AND s.account_id = a.id
GROUP BY a.id
ORDER BY RAND() ;
Create statements:
/*Table structure for table `accounts` */
CREATE TABLE `accounts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`delivery_id` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`delivery_type` tinyint(1) NOT NULL DEFAULT '1',
`username` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'SHA1',
`phone` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`street` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`number` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`postcode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`area_id` int(11) DEFAULT NULL,
`city_id` int(11) NOT NULL DEFAULT '1',
`mon_from` time DEFAULT NULL,
`mon_to` time DEFAULT NULL,
`tue_from` time DEFAULT NULL,
`tue_to` time DEFAULT NULL,
`wed_from` time DEFAULT NULL,
`wed_to` time DEFAULT NULL,
`thu_from` time DEFAULT NULL,
`thu_to` time DEFAULT NULL,
`fri_from` time DEFAULT NULL,
`fri_to` time DEFAULT NULL,
`sat_from` time DEFAULT NULL,
`sat_to` time DEFAULT NULL,
`sun_from` time DEFAULT NULL,
`sun_to` time DEFAULT NULL,
`def_cuis` int(11) DEFAULT NULL,
`sec_cuis` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
`edt` smallint(6) DEFAULT NULL,
`ect` tinyint(2) DEFAULT NULL,
`delay_edt` tinyint(4) DEFAULT NULL,
`company_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`doy` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`vat` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`activity` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`company_address` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`date_added` date DEFAULT NULL,
`online_date` date DEFAULT NULL,
`next_payment` date DEFAULT NULL,
`payment_range` tinyint(4) DEFAULT NULL,
`minimum_order` decimal(4,2) NOT NULL DEFAULT '0.00',
`minimum_order_c` decimal(4,2) NOT NULL DEFAULT '0.00',
`commission` tinyint(2) DEFAULT NULL,
`aff_commission` tinyint(2) DEFAULT NULL,
`supervisor_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`supervisor_phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`contact_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`contact_phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`dealer_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`comments` text COLLATE utf8_unicode_ci,
`catalogue_desc` text COLLATE utf8_unicode_ci,
`distance` tinyint(1) NOT NULL DEFAULT '0',
`lat` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`lng` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`polygon` text COLLATE utf8_unicode_ci,
`invoice` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`ticket_account` tinyint(1) NOT NULL DEFAULT '0',
`cheap_account` tinyint(1) NOT NULL DEFAULT '0',
`call_notification` tinyint(1) NOT NULL DEFAULT '1',
`closed_notification` tinyint(1) NOT NULL DEFAULT '1',
`b2b_printrow` tinyint(1) NOT NULL DEFAULT '0',
`superuser_id` int(11) DEFAULT NULL,
`tech_issue` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0=ok,1=tech_issue',
`online` timestamp NULL DEFAULT NULL,
`call_status` tinyint(1) NOT NULL DEFAULT '0',
`call_action` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`call_counter` tinyint(1) NOT NULL DEFAULT '0',
`failed_call_counter` tinyint(3) NOT NULL DEFAULT '0',
`next_call` timestamp NULL DEFAULT NULL,
`account_status` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`permalink` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`brand_text` text COLLATE utf8_unicode_ci,
`last_update` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `delivery_id` (`delivery_id`),
KEY `delivery_type` (`delivery_type`),
KEY `invoice` (`invoice`),
KEY `active_delivery_type` (`active`,`delivery_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1202 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Table structure for table `cats` */
CREATE TABLE `cats` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`delivery_id` bigint(20) DEFAULT NULL,
`cuisine_id` int(11) DEFAULT NULL,
`section_id` int(11) NOT NULL DEFAULT '1',
`name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`description` text COLLATE utf8_unicode_ci NOT NULL,
`max_toppings` tinyint(1) NOT NULL DEFAULT '2',
`fan_id` int(11) DEFAULT NULL,
`ordering` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `delivery_id` (`delivery_id`),
KEY `cuisine_id` (`cuisine_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6756 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Table structure for table `cuisines` */
CREATE TABLE `cuisines` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`permalink` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` text COLLATE utf8_unicode_ci,
`title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`comments` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Table structure for table `cuisines2cats` */
CREATE TABLE `cuisines2cats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cuisine_id` int(11) NOT NULL,
`cat_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `cuisine_id_cat_id` (`cuisine_id`,`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8436 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Table structure for table `deliveries` */
CREATE TABLE `deliveries` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`user_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`street` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`number` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`postcode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`invoice_email` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`area_id` int(11) DEFAULT NULL,
`city_id` bigint(20) NOT NULL,
`ticket` tinyint(4) NOT NULL DEFAULT '0',
`cheap` tinyint(1) NOT NULL DEFAULT '1',
`premium_until` date DEFAULT NULL,
`mon_from` time DEFAULT NULL,
`mon_to` time DEFAULT NULL,
`tue_from` time DEFAULT NULL,
`tue_to` time DEFAULT NULL,
`wed_from` time DEFAULT NULL,
`wed_to` time DEFAULT NULL,
`thu_from` time DEFAULT NULL,
`thu_to` time DEFAULT NULL,
`fri_from` time DEFAULT NULL,
`fri_to` time DEFAULT NULL,
`sat_from` time DEFAULT NULL,
`sat_to` time DEFAULT NULL,
`sun_from` time DEFAULT NULL,
`sun_to` time DEFAULT NULL,
`edt` int(11) DEFAULT NULL COMMENT 'Estimate Delivery Time',
`discount` tinyint(4) DEFAULT NULL,
`biz_discount` tinyint(4) DEFAULT NULL,
`invoice` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`minimum_order` decimal(4,2) DEFAULT NULL,
`supervisor_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`supervisor_phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`contact_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`contact_phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`dealer_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`closed_notification_phone` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`order_notification_phone` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`company_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`doy` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`vat` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`activity` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`comments` text COLLATE utf8_unicode_ci,
`def_cuis` int(11) DEFAULT NULL,
`sec_cuis` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
`commission` tinyint(2) NOT NULL,
`aff_commission` tinyint(2) DEFAULT NULL,
`b2b_autoprint` tinyint(1) NOT NULL DEFAULT '1',
`b2b_showpopup` tinyint(1) NOT NULL DEFAULT '1',
`b2b_printrow` tinyint(4) NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '1',
`permalink` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`brand_text` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`),
KEY `id_active` (`id`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Table structure for table `products` */
CREATE TABLE `products` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`cat_id` int(8) DEFAULT NULL,
`delivery_id` int(8) DEFAULT NULL,
`name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`description` text COLLATE utf8_unicode_ci,
`new_product` tinyint(4) NOT NULL DEFAULT '0',
`vegetarian` tinyint(1) NOT NULL DEFAULT '0',
`spicy` tinyint(1) NOT NULL DEFAULT '0',
`promo` tinyint(1) NOT NULL DEFAULT '0',
`upsell` tinyint(1) NOT NULL DEFAULT '0',
`ordering` smallint(6) DEFAULT NULL,
`max_toppings` tinyint(4) DEFAULT NULL,
`free_toppings` tinyint(4) NOT NULL DEFAULT '0',
`comments` text COLLATE utf8_unicode_ci,
`date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`dont_allow_extra_topps` tinyint(1) NOT NULL DEFAULT '0',
`fan_id` int(11) DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `cat_id` (`cat_id`),
KEY `delivery_id` (`delivery_id`),
KEY `active` (`active`),
KEY `max_toppings` (`max_toppings`),
KEY `free_toppings` (`free_toppings`)
) ENGINE=InnoDB AUTO_INCREMENT=41487 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Table structure for table `specs` */
CREATE TABLE `specs` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`product_id` int(8) DEFAULT NULL,
`account_id` int(8) DEFAULT NULL,
`pan_id` int(8) DEFAULT NULL,
`size_id` int(8) DEFAULT NULL,
`price` decimal(5,2) DEFAULT NULL,
`topping_price` decimal(4,2) DEFAULT NULL,
`available` tinyint(1) NOT NULL DEFAULT '1',
`mon_from` time DEFAULT NULL,
`mon_to` time DEFAULT NULL,
`tue_from` time DEFAULT NULL,
`tue_to` time DEFAULT NULL,
`wed_from` time DEFAULT NULL,
`wed_to` time DEFAULT NULL,
`thu_from` time DEFAULT NULL,
`thu_to` time DEFAULT NULL,
`fri_from` time DEFAULT NULL,
`fri_to` time DEFAULT NULL,
`sat_from` time DEFAULT NULL,
`sat_to` time DEFAULT NULL,
`sun_from` time DEFAULT NULL,
`sun_to` time DEFAULT NULL,
`bank_from` time DEFAULT NULL,
`bank_to` time DEFAULT NULL,
`vat` decimal(4,2) DEFAULT NULL,
`featured` tinyint(1) NOT NULL DEFAULT '0',
`extension` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '0',
`existing` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `pan_id` (`pan_id`),
KEY `size_id` (`size_id`),
KEY `topping_price` (`topping_price`),
KEY `available` (`available`),
KEY `active` (`active`),
KEY `price` (`price`),
KEY `existing` (`existing`),
KEY `account_id_product_id` (`account_id`,`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=242078 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED;
/*Table structure for table `time_schedule` */
CREATE TABLE `time_schedule` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
`day` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
`time_from` time DEFAULT NULL,
`time_to` time DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `account_id` (`account_id`,`day`)
) ENGINE=InnoDB AUTO_INCREMENT=5357 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Explains:
MySQL 5.5.32 Explain:
+----+-------------+-------+--------+------------------------------------------+------------+---------+----------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------------+------------+---------+----------------------------------+------+---------------------------------+
| 1 | SIMPLE | cc | ref | cuisine_id,cat_id | cuisine_id | 4 | const | 213 | Using temporary; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY,delivery_id | PRIMARY | 8 | delivera_deliveras.cc.cat_id | 1 | Using where |
| 1 | SIMPLE | p | ref | PRIMARY,cat_id | cat_id | 5 | delivera_deliveras.c.id | 3 | Using where; Using index |
| 1 | SIMPLE | s | ref | product_id,account_id | product_id | 5 | delivera_deliveras.p.id | 4 | Using where |
| 1 | SIMPLE | a | eq_ref | PRIMARY,delivery_id,delivery_type,active | PRIMARY | 8 | delivera_deliveras.s.account_id | 1 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY,active | PRIMARY | 8 | delivera_deliveras.a.delivery_id | 1 | Using where |
| 1 | SIMPLE | re | ref | account_id,active,approved | account_id | 4 | delivera_deliveras.a.id | 1 | |
| 1 | SIMPLE | ts | ref | account_id,day | account_id | 4 | delivera_deliveras.a.id | 2 | Using where |
| 1 | SIMPLE | cu | ALL | PRIMARY | NULL | NULL | NULL | 34 | Using where; Using join buffer |
+----+-------------+-------+--------+------------------------------------------+------------+---------+----------------------------------+------+---------------------------------+
Percona 5.6.14 Explain:
+----+-------------+-------+--------+--------------------------------------------------+------------+---------+-------------------------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------+------------+---------+-------------------------+------+------------------------------------------------+
| 1 | SIMPLE | cc | ref | cuisine_id,cat_id | cuisine_id | 4 | const | 213 | Using temporary; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY,delivery_id | PRIMARY | 8 | perconadb.cc.cat_id | 1 | Using where |
| 1 | SIMPLE | p | ref | PRIMARY,cat_id | cat_id | 5 | perconadb.c.id | 3 | Using where; Using index |
| 1 | SIMPLE | s | ref | product_id,account_id | product_id | 5 | perconadb.p.id | 2 | Using where |
| 1 | SIMPLE | a | eq_ref | PRIMARY,delivery_id,delivery_type,invoice,active | PRIMARY | 8 | perconadb.s.account_id | 1 | Using where |
| 1 | SIMPLE | re | ref | account_id,active,approved | account_id | 4 | perconadb.a.id | 1 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY,active | PRIMARY | 8 | perconadb.a.delivery_id | 1 | Using where |
| 1 | SIMPLE | ts | ref | account_id,day | account_id | 4 | perconadb.a.id | 3 | Using index condition; Using where |
| 1 | SIMPLE | cu | ALL | PRIMARY | NULL | NULL | NULL | 32 | Range checked for each record (index map: 0x1) |
+----+-------------+-------+--------+--------------------------------------------------+------------+---------+-------------------------+------+------------------------------------------------+
Percona my.cnf:
[mysqld]
# GENERAL #
user = mysql
default_storage_engine = MyISAM
sql-mode = NO_ENGINE_SUBSTITUTION
performance_schema = OFF
back_log = 2000
thread_handling = pool-of-threads
tmpdir = /dev/shm
# MyISAM #
key_buffer_size = 256M
#myisam_recover = BACKUP,FORCE
myisam_sort_buffer_size = 128M
group_concat_max_len = 4096
max_seeks_for_key = 1000
myisam_use_mmap = 1
# SAFETY #
max_allowed_packet = 1G
max_connect_errors = 1000000
local-infile = 0
# CACHES AND LIMITS #
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 6M
query_cache_min_res_unit = 2K
max_connections = 275
max_user_connections = 250
thread_cache_size = 1000
open_files_limit = 50K
table_definition_cache = 4K
table_open_cache = 10K
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
#optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'
# INNODB #
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_buffer_pool_size = 6G
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 10000
innodb_log_buffer_size = 32M
innodb_file_format = Barracuda
max_prepared_stmt_count = 1048560
innodb_purge_threads = 4
innodb_sched_priority_cleaner = 39
innodb_adaptive_hash_index_partitions = 64
# Time-out's #
connect_timeout=30
interactive_timeout=360
wait_timeout=360
Explain after applying suggested indexes and running the provided query by DRapp:
+----+-------------+------------+--------+----------------------------------------------------------------+----------------------------+---------+----------------------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+----------------------------------------------------------------+----------------------------+---------+----------------------------+------+-----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3367 | Using temporary; Using filesort |
| 1 | PRIMARY | a | eq_ref | PRIMARY,delivery_id,delivery_type,invoice,active_delivery_type | PRIMARY | 8 | PreQuery.id | 1 | Using where |
| 1 | PRIMARY | re | ref | account_id_approved_active | account_id_approved_active | 6 | PreQuery.id,const,const | 1 | Using where; Using index |
| 1 | PRIMARY | d | eq_ref | PRIMARY,id_active | PRIMARY | 8 | perconadb.a.delivery_id | 1 | Using where |
| 1 | PRIMARY | c | ref | PRIMARY,delivery_id | delivery_id | 9 | perconadb.a.delivery_id | 7 | Using where; Using index |
| 1 | PRIMARY | cc | ref | cuisine_id_cat_id | cuisine_id_cat_id | 8 | const,perconadb.c.id | 1 | Using where; Using index |
| 1 | PRIMARY | p | ref | PRIMARY,cat_id | cat_id | 5 | perconadb.c.id | 3 | Using where; Using index |
| 1 | PRIMARY | s | ref | account_id_product_id | account_id_product_id | 10 | PreQuery.id,perconadb.p.id | 1 | Using where; Using index |
| 1 | PRIMARY | cu | ALL | PRIMARY | NULL | NULL | NULL | 32 | Range checked for each record (index map: 0x1) |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 3367 | Using temporary; Using filesort |
| 2 | DERIVED | ts1 | ref | account_id | account_id | 15 | JustAccounts.id,const | 1 | Using index condition |
| 3 | DERIVED | a | range | PRIMARY,delivery_id,delivery_type,invoice,active_delivery_type | active_delivery_type | 2 | NULL | 481 | Using index condition; Using where; Using temporary |
| 3 | DERIVED | d | eq_ref | PRIMARY,id_active | PRIMARY | 8 | perconadb.a.delivery_id | 1 | Using where |
| 3 | DERIVED | c | ref | PRIMARY,delivery_id | delivery_id | 9 | perconadb.a.delivery_id | 7 | Using where; Using index |
| 3 | DERIVED | cc | ref | cuisine_id_cat_id | cuisine_id_cat_id | 8 | const,perconadb.c.id | 1 | Using where; Using index |
+----+-------------+------------+--------+----------------------------------------------------------------+----------------------------+---------+----------------------------+------+-----------------------------------------------------+
I'm going to throw my hat into this and see if anything can help... First, to benefit the querying conditions, I would start by adding the following indexes to the respective tables. By having the multiple key columns, and not just individual can significantly help performance.
TABLE INDEX
Accounts (Active, DeliveryType)
Time_Schedule (account_id, day)
Deliveries (id, active)
cuisines2cats (cuisine_id, cat_id )
Reviews (account_id, approved, active )
ON the specs table, I didn't see the account_id as part of the table structure... however, if it SHOULD be there, I would have an index ON
Specs (account_id, product_id )
Next, in your query, you have some conditions based on active and delivery type status = ex: '1' or '2'. These are numeric fields. You should leave them as just 1 and 2 respectively so it doesn't have to do data-type conversion for comparison.
TRY THE ABOVE FIRST on just the indexes. Check any performance improvement.
Per our outside efforts, just wanted to provide the final working query.
SELECT
FQ.*,
COUNT(DISTINCT re.id) AS Reviews_Comm
FROM
(SELECT
a.*,
IF(
JustTimes.account_id IS NULL,
"YES",
"no"
) AS OPEN,
d.cheap,
d.name AS delivery_name,
cu.name AS cuisine,
IFNULL(a.sec_cuis, d.sec_cuis) AS SECONDARY_CUIS,
d.ticket,
IF(
TIMESTAMPDIFF(SECOND, a.online, NOW()) <= 120,
1,
0
) AS SECONDARY_ONLINE
FROM
cuisines2cats cc
JOIN cuisines AS cu
ON cc.cuisine_id = cu.id
JOIN cats c
ON cc.cat_id = c.id
JOIN products p
ON c.id = p.cat_id
AND p.active = 1
JOIN specs s
ON p.id = s.product_id
AND s.active = 1
JOIN accounts a
ON s.account_id = a.id
AND c.delivery_id = a.delivery_id
AND a.Active = 1
AND a.Delivery_type IN (1, 2)
JOIN deliveries d
ON a.delivery_id = d.id
AND d.active = 1
LEFT JOIN
(SELECT DISTINCT
ts.account_id
FROM
time_schedule ts
WHERE ts.day = 'mon'
AND ts.time_from IS NOT NULL
AND ts.time_to IS NOT NULL
AND (
(ts.time_from = ts.time_to)
OR (
ts.time_from <= NOW()
AND NOW() <= ts.time_to
)
OR (
ts.time_to < ts.time_from
AND NOT (ts.time_to < NOW())
AND NOW() < ts.time_from
)
)) AS JustTimes
ON a.id = JustTimes.account_id
WHERE cc.cuisine_id = 3
AND cu.id IN (a.def_cuis, d.def_cuis)) AS FQ
LEFT JOIN reviews re
ON FQ.id = re.account_id
AND re.approved = 1
AND re.active = 1
GROUP BY FQ.id
ORDER BY RAND();