mysql 'slow queries log' return a huge number of rows examined but explain seems ok

Question

The sql query

# Query_time: 16.536276  Lock_time: 0.000159 Rows_sent: 756  Rows_examined: 8392194
SET timestamp=1555422526;
SELECT c.id AS c__id, c.company_id AS c__company_id, 
        c.ordinary_price AS c__ordinary_price, c.nights AS c__nights, 
        c.food_type AS c__food_type, c.period AS c__period, 
        c.period_desc AS c__period_desc, c.extra AS c__extra, 
        c.coupons_bought AS c__coupons_bought, c.coupon_price AS c__coupon_price, 
        c.coordinates AS c__coordinates, c.best_price AS c__best_price, 
        c.from_price AS c__from_price, c.end_datetime AS c__end_datetime, 
        c.hide_clock AS c__hide_clock, c.hide_discount AS c__hide_discount, 
        c.booking_hotel_id AS c__booking_hotel_id, c.title AS c__title, 
        c.option_people AS c__option_people, c.option_room AS c__option_room, 
        c.option_period AS c__option_period, c.city AS c__city, 
        c2.id AS c2__id, c2.people AS c2__people, c2.room AS c2__room, 
            (  SELECT c8.url AS c8__url 
               FROM campaign_images c8 
               WHERE (c8.campaign_id = c.id 
                 AND  c8.photo_type = 'list') 
               ORDER BY c8.ordering ASC LIMIT 1
            ) AS c__0 
FROM campaign c 
    LEFT JOIN campaign_options c2 ON c.id = c2.campaign_id 
        AND (c2.active = 1) 
    LEFT JOIN city_in_campaign c3 ON c.id = c3.campaign_id 
    LEFT JOIN city c4 ON c3.city_id = c4.id 
    LEFT JOIN company c5 ON c.company_id = c5.id 
    LEFT JOIN campaign_in_category c6 ON c.id = c6.campaign_id 
    LEFT JOIN campaign_in_group c7 ON c.id = c7.campaign_id 
WHERE c.id IN ('13308', '13281', '13265') 
AND (c.status IN ('published') 
AND c.start_datetime <= NOW() 
AND c.end_datetime >= NOW() 
AND c5.id = '2111' 
AND c.id != '14624' 
AND (c7.group_id in (1) 
    OR c7.group_id is NULL
    )
) 
ORDER BY c.coupon_expire_datetime ASC;

create table campaign

CREATE TABLE `campaign` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `company_id` int(11) DEFAULT NULL,
 `source_id` int(11) DEFAULT NULL,
 `secondary_source_id` int(11) DEFAULT NULL,
 `source_hotel_id` int(11) DEFAULT NULL,
 `booking_hotel_id` int(11) DEFAULT NULL,
 `booking_board_id` int(11) DEFAULT NULL,
 `booking_rate` varchar(100) DEFAULT NULL,
 `ordinary_price` decimal(9,2) NOT NULL,
 `best_price` tinyint(1) NOT NULL DEFAULT '0',
 `from_price` tinyint(1) NOT NULL DEFAULT '0',
 `status` varchar(10) DEFAULT 'draft',
 `type` varchar(20) DEFAULT 'other',
 `deal_status` varchar(20) DEFAULT 'in_progress',
 `coupon_price` decimal(9,2) NOT NULL,
 `cosmote_discount` decimal(5,2) DEFAULT NULL,
 `nights` int(11) NOT NULL DEFAULT '0',
 `period` varchar(255) DEFAULT NULL,
 `period_desc` varchar(1000) DEFAULT NULL,
 `hide_period` tinyint(1) NOT NULL DEFAULT '0',
 `food_type` varchar(100) DEFAULT NULL,
 `stars` varchar(100) DEFAULT NULL,
 `adults` tinyint(1) DEFAULT NULL,
 `childs` tinyint(1) DEFAULT NULL,
 `extra` varchar(255) DEFAULT NULL,
 `best_point` varchar(511) DEFAULT NULL,
 `extra_night_price` decimal(9,2) NOT NULL DEFAULT '0.00',
 `high_season_price` decimal(9,2) NOT NULL DEFAULT '0.00',
 `high_season_desc` varchar(500) DEFAULT NULL,
 `high_season_extra_night_price` decimal(9,2) NOT NULL DEFAULT '0.00',
 `family_packages_desc` varchar(1000) DEFAULT NULL,
 `coordinates` varchar(70) DEFAULT NULL,
 `start_datetime` datetime DEFAULT NULL,
 `end_datetime` datetime DEFAULT NULL,
 `coupon_expire_datetime` datetime DEFAULT NULL,
 `active` tinyint(4) NOT NULL DEFAULT '1',
 `city` varchar(255) DEFAULT NULL,
 `min_coupons` int(11) NOT NULL DEFAULT '0',
 `is_global` tinyint(4) NOT NULL DEFAULT '0',
 `hide_clock` tinyint(1) NOT NULL DEFAULT '0',
 `hide_discount` tinyint(1) NOT NULL DEFAULT '0',
 `hide_purchases` tinyint(1) NOT NULL DEFAULT '0',
 `booking_enabled` tinyint(1) NOT NULL DEFAULT '0',
 `booking_phone` varchar(50) DEFAULT NULL,
 `refresh` tinyint(1) NOT NULL DEFAULT '0',
 `installments` tinyint(1) NOT NULL DEFAULT '1',
 `receipt` enum('0','1') NOT NULL DEFAULT '0',
 `newsletters_sent` tinyint(4) NOT NULL DEFAULT '0',
 `max_coupons` int(11) NOT NULL DEFAULT '0',
 `max_coupons_per_user` int(11) DEFAULT NULL,
 `coupons_bought` int(11) unsigned NOT NULL DEFAULT '0',
 `fake_orders` int(11) DEFAULT '0',
 `title` varchar(255) DEFAULT NULL,
 `newsletter_title` varchar(500) DEFAULT NULL,
 `linkwise_title` varchar(500) DEFAULT NULL,
 `option_title` varchar(255) DEFAULT NULL,
 `option_title_en` varchar(255) DEFAULT NULL,
 `option_people` varchar(150) DEFAULT NULL,
 `option_room` varchar(150) DEFAULT NULL,
 `option_period` varchar(150) DEFAULT NULL,
 `name` varchar(1200) DEFAULT NULL,
 `description` text,
 `highlights` text,
 `coupon_instructions` text,
 `show_in_recent_deals` tinyint(4) NOT NULL DEFAULT '1',
 `youtube_video_id` varchar(100) DEFAULT NULL,
 `in_side` tinyint(4) NOT NULL DEFAULT '0',
 `family` tinyint(1) NOT NULL DEFAULT '0',
 `send_newsletter` tinyint(4) NOT NULL DEFAULT '1',
 `resend_newsletter` tinyint(4) NOT NULL DEFAULT '0',
 `modified_datetime` timestamp NULL DEFAULT NULL,
 `created_datetime` datetime NOT NULL,
 `ordering` int(11) unsigned DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `company_id_idx` (`company_id`),
 KEY `user_id_idx` (`user_id`),
 KEY `status_indx` (`status`),
 KEY `str_dt_indx` (`start_datetime`),
 KEY `end_dt_indx` (`end_datetime`),
 KEY `side_indx` (`in_side`),
 KEY `ord_indx` (`ordering`),
 KEY `global_indx` (`is_global`),
 KEY `act_indx` (`active`),
 KEY `coup_expr_index` (`coupon_expire_datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=14788 DEFAULT CHARSET=utf8

create table campaign_options

CREATE TABLE `campaign_options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `campaign_id` int(11) NOT NULL,
 `coupons_bought` int(11) unsigned NOT NULL DEFAULT '0',
 `name` varchar(255) NOT NULL,
 `name_en` varchar(255) DEFAULT NULL,
 `people` varchar(100) DEFAULT NULL,
 `room` varchar(100) DEFAULT NULL,
 `food` varchar(100) DEFAULT NULL,
 `period` varchar(100) DEFAULT NULL,
 `coupon_price` decimal(9,2) NOT NULL,
 `extra_night_price` decimal(9,2) NOT NULL DEFAULT '0.00',
 `high_season_price` decimal(9,2) DEFAULT '0.00',
 `high_season_extra_night_price` decimal(9,2) NOT NULL DEFAULT '0.00',
 `modified_datetime` timestamp NULL DEFAULT NULL,
 `created_datetime` datetime NOT NULL,
 `main` tinyint(1) NOT NULL DEFAULT '0',
 `family` tinyint(1) NOT NULL DEFAULT '0',
 `active` tinyint(4) NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`),
 KEY `campaign_id_idx` (`campaign_id`),
 KEY `active_indx` (`active`),
 KEY `family_indx` (`family`),
 KEY `main_indx` (`main`)
) ENGINE=InnoDB AUTO_INCREMENT=48990 DEFAULT CHARSET=utf8

create table city_in_campaign

CREATE TABLE `city_in_campaign` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `campaign_id` int(11) DEFAULT NULL,
 `city_id` int(11) DEFAULT NULL,
 `ordering` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `campaign_id_idx` (`campaign_id`),
 KEY `city_id_idx` (`city_id`),
 KEY `order_indx` (`ordering`)
) ENGINE=InnoDB AUTO_INCREMENT=227176 DEFAULT CHARSET=utf8

create table city

CREATE TABLE `city` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `country_id` int(11) DEFAULT NULL,
 `name` varchar(100) DEFAULT NULL,
 `is_active` tinyint(3) unsigned DEFAULT NULL,
 `modified_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `country_id_idx` (`country_id`),
 KEY `is_active_indx` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=utf8

create table company

CREATE TABLE `company` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `ref_id` int(11) DEFAULT NULL,
 `name` varchar(100) DEFAULT NULL,
 `description` text,
 `logo` varchar(255) DEFAULT NULL,
 `phone_number` varchar(100) DEFAULT NULL,
 `address` varchar(200) DEFAULT NULL,
 `coordinates` varchar(70) DEFAULT NULL,
 `email` varchar(100) DEFAULT NULL,
 `website` varchar(255) DEFAULT NULL,
 `skype_name` varchar(50) DEFAULT NULL,
 `icq_number` varchar(255) DEFAULT NULL,
 `payment_information` text,
 `extra1` text,
 `extra2` text,
 `extra3` text,
 `video` varchar(500) DEFAULT NULL,
 `checked` tinyint(1) NOT NULL DEFAULT '0',
 `ordering` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `checked_indx` (`checked`),
 KEY `ordering_indx` (`ordering`)
) ENGINE=InnoDB AUTO_INCREMENT=2519 DEFAULT CHARSET=utf8

create table campaign_in_category

CREATE TABLE `campaign_in_category` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `campaign_id` int(11) NOT NULL,
 `category_id` int(11) DEFAULT NULL,
 `ordering` int(11) NOT NULL DEFAULT '0',
 `created_datetime` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `campaign_id_idx` (`campaign_id`),
 KEY `category_id_idx` (`category_id`),
 KEY `order_indx` (`ordering`)
) ENGINE=InnoDB AUTO_INCREMENT=457080 DEFAULT CHARSET=utf8

create table campaign_in_group

CREATE TABLE `campaign_in_group` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `campaign_id` int(11) DEFAULT NULL,
 `group_id` int(11) DEFAULT NULL,
 `created_datetime` datetime DEFAULT NULL,
 `modified_datetime` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `camp_group_indx` (`campaign_id`,`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1175 DEFAULT CHARSET=utf8

campaign total rows: 12,657

campaign_options total rows: 43,714

city_in_campaign total rows: 15,162

city total rows: 215

company total rows: 1,756

campaign_in_category total rows: 38,817

campaign_in_group total rows: 395

explain of the query

When you ask a question about query optimization, you should run `SHOW CREATE TABLE ` for any tables referenced by your query, and include the output in your question. Don't make us guess which indexes, data types, and constraints you have. — Bill Karwin, Apr 16 '19 at 15:19
i have added "create table" query and total rows for each table — Panos, Apr 16 '19 at 15:45
Please don't use `LEFT` when the 'right' table is required -- This applies to at least c5 and c7. You check the others. — Rick James, Apr 24 '19 at 19:22

score 0 · Answer 1 · answered Apr 16 '19 at 15:13

0

which ones have index

it looks you need composite index because file sorting.

usage:

CREATE INDEX index_name 
ON table_name(c2,c3,c4);

answered Apr 16 '19 at 15:13

Şafak Çıplak

889
6
12

score 0 · Answer 2 · answered Apr 16 '19 at 15:28

You are getting above result because some time o/p of explain gives different result as response of query of prod.
For optimization of query you can't have a fix answer. it varies from case to case. As for this situation I think you need to perform performance testing of this query. You can do this by inserting some records in all referenced table and then checking it's performance using explain.

score 0 · Answer 3 · answered Apr 24 '19 at 19:24

0

LEFT JOIN campaign_in_category c6 ON c.id = c6.campaign_id

seems to be totally useless. But the Optimizer may not realize it. Remove it and any other dead code.

answered Apr 24 '19 at 19:24

Rick James

135,179
13
127
222

mysql 'slow queries log' return a huge number of rows examined but explain seems ok

3 Answers3

usage: