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

mysql workbench
mysql download
mysql free download
mysql tutorial
mysql server
mysql commands
how to install mysql
mysql online

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

which ones have index


it looks you need composite index because file sorting.

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

MySQL, The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows. Databases are useful for storing� We've just got new applications for Mysql Jobs. Don't miss out!

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.

PHP: MySQL Database, Oracle MySQL Database Service is a fully managed database service that enables organizations to deploy cloud native applications using the world's most � Huge Selection & Low Prices. Free Delivery On Eligible Orders. Buy Now

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.

MySQL Database Service, MySQL is a database management system that allows you to manage relational databases. It is open source software backed by Oracle. It means you can use� MySQL Cluster enables users to meet the database challenges of next generation web, cloud, and communications services with uncompromising scalability, uptime and agility. Learn More » Over 2000 ISVs, OEMs, and VARs rely on MySQL as their products' embedded database to make their applications, hardware and appliances more competitive, bring them to market faster, and lower their cost of goods

What Is MySQL? & Why It Is the World's Most Popular Open Source , MySQL is the most popular Open Source Relational SQL Database Management System. MySQL is one of the best RDBMS being used for developing various� MySQL (/ ˌ m aɪ ˌ ɛ s ˌ k juː ˈ ɛ l / "My S-Q-L") is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.

MySQL Tutorial, MySQL IN 10 MINUTES (2020) | Introduction to Databases, SQL, & MySQL. 46,356 views46K Duration: 11:30 Posted: Jan 22, 2019 Oracle MySQL Database Service is a fully managed database service that enables organizations to deploy cloud native applications using the world’s most popular open source database. It is 100% developed, managed, and supported by the MySQL team. Fastest availability of updates, new features, and

MySQL IN 10 MINUTES (2020), MySQL is a free, open source RDBMS (relational database management system) that utilizes SQL (structured query language). SQL is a language used to add, access and manage database content. The language is known for its proven reliability, quick processing, flexibility and ease of use. MySQL is a specialized database software program that makes storing and retrieving data as efficient as possible.

Comments
  • When you ask a question about query optimization, you should run SHOW CREATE TABLE <tablename> 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.
  • i have added "create table" query and total rows for each table
  • Please don't use LEFT when the 'right' table is required -- This applies to at least c5 and c7. You check the others.