Mysql – Best strategy to correctly index complex join query

MySQLmysql-5.7performance-tuning

This is the query running on MySQL 5.7 with INNODB:

SELECT SQL_CALC_FOUND_ROWS
    calendar.id_calendar AS calendar_id,
    calendar.event_datetime AS calendar_event_datetime
FROM
    users
    INNER JOIN calendar ON (users.login_enabled = 1 AND calendar.id_agente = users.id)
    LEFT  JOIN customers ON customers.id_customer = calendar.id_customer
    LEFT  JOIN ads ON ads.id = calendar.id_ads
WHERE
    calendar.hidden = 0
     AND calendar.id_agenzia = 5134 AND users.id IN (5134,6816,5155,6181,5154,8783,5172,5143,11360) 
     AND eventi_custom_id IN (1,2,3,15,4,5,7,8,9,10,11,12,13,14,6) 
    AND (calendar.commento LIKE '%figline%' OR customers.surname LIKE '%figline%' OR customers.name LIKE '%figline%' OR customers.company_name LIKE '%figline%' OR ads.agency_code LIKE '%figline%')
ORDER BY
    calendar.event_datetime DESC LIMIT 0,50

An explain shows me a very basic index usage even if I tried to create better ones according with MySQL index optimization tips found on Mysql official docs.

Biggest problem is indexing table Calendar which seems to use partially some index but gives me "using where" on explain's extra column.
I tried to create many index for table Calendar but they are always partially used.

id  select_type   table     type     possible_keys                                                                 key                          key_len  ref                                rows  filtered   Extra       
1   SIMPLE        calendar  ref      id_agente-event_datetime,id_agenzia-event_datetime,id_                        id_agenzia-evento_datetime   5        const                              1     5.00       Using where    
                                     id_agente-id_agenzia-hidden-event_datetime,id_agente-hidden-event_datetime,
                                     eventi_custom_id,id_agenzia,prova  
1   SIMPLE        users     eq_ref   PRIMARY,login_enabled,id-logo_img,id                                          PRIMARY                      4        prossima-casa.calendar.id_agente   1     50.00      Using where    
1   SIMPLE        customers eq_ref   PRIMARY                                                                       PRIMARY                      4        prossima-casa.calendar.id_customer 1     100.00    
1   SIMPLE        ads       eq_ref   PRIMARY                                                                       PRIMARY                      4        prossima-casa.calendar.id_ads      1     100.00     Using where

Here's table defs:

CREATE TABLE `calendar` (
 `id_calendar` int(11) NOT NULL AUTO_INCREMENT,
 `parent_id_calendar` int(11) DEFAULT NULL,
 `id_ripetizione` varchar(32) DEFAULT NULL,
 `childs_count` enum('0','1','2','3') NOT NULL DEFAULT '0',
 `id_ads` int(11) DEFAULT NULL,
 `id_customer` int(11) DEFAULT NULL,
 `id_agente` int(11) NOT NULL,
 `id_agenzia` int(11) NOT NULL,
 `event_datetime` datetime NOT NULL,
 `event_length` time NOT NULL DEFAULT '00:15:00',
 `commento` text,
 `hidden` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `eventi_custom_id` tinyint(2) NOT NULL DEFAULT '1',
 `event_in_out` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_in_evidenza` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_confirmed` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_confirmed_prop` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_eseguito` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_only_for_me` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `google_id` varchar(255) DEFAULT NULL,
 `commento_pubblico` text',
 `flag_preview_pubblica` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `creator_users_id` int(11) NOT NULL,
 `creator_time` datetime NOT NULL,
 `last_editor_time` datetime NOT NULL,
 `last_editor_user_id` int(11) DEFAULT '0',
 `last_editor_user_ip` varchar(30) DEFAULT NULL,
 `external_id_calendar` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id_calendar`),
 KEY `id_customer-event_datetime` (`id_customer`,`event_datetime`),
 KEY `id_agente-event_datetime` (`id_agente`,`event_datetime`),
 KEY `id_agenzia-event_datetime` (`id_agenzia`,`event_datetime`),
 KEY `id_agente-id_agenzia-hidden-event_datetime` (`id_agente`,`id_agenzia`,`hidden`,`event_datetime`),
 KEY `parent_id_calendar-hidden` (`parent_id_calendar`,`hidden`),
 KEY `id_agente-hidden-event_datetime` (`id_agente`,`hidden`,`event_datetime`,`flag_in_evidenza`,`creator_users_id`),
 KEY `eventi_custom_id` (`eventi_custom_id`),
 KEY `id_ads` (`id_ads`),
 KEY `event_datetime` (`event_datetime`),
 KEY `id_ripetizione` (`id_ripetizione`),
 KEY `external_id_calendar` (`external_id_calendar`),
 KEY `id_customer-hidden` (`id_customer`,`hidden`) USING BTREE,
 KEY `id_agenzia` (`id_agenzia`,`hidden`,`event_datetime`) USING BTREE,
 KEY `prova` (`id_agente`,`id_customer`,`id_ads`) USING BTREE,
 CONSTRAINT `calendar_ibfk_18` FOREIGN KEY (`id_customer`) REFERENCES `customers` (`id_customer`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `calendar_ibfk_19` FOREIGN KEY (`id_agente`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `calendar_ibfk_20` FOREIGN KEY (`id_agenzia`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `calendar_ibfk_5` FOREIGN KEY (`id_ads`) REFERENCES `ads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3977444 DEFAULT CHARSET=utf8


CREATE TABLE `customers` (
 `id_customer` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) DEFAULT NULL,
 `surname` varchar(50) DEFAULT NULL,
 `email` varchar(100) DEFAULT NULL,
 `email2` varchar(100) DEFAULT NULL,
 `email_pec` varchar(100) DEFAULT NULL,
 `skype` varchar(100) DEFAULT NULL,
 `facebook` varchar(100) DEFAULT NULL,
 `linkedin` varchar(100) DEFAULT NULL,
 `tel` varchar(15) DEFAULT NULL,
 `tel2` varchar(20) DEFAULT NULL,
 `cell` varchar(20) DEFAULT NULL,
 `fax` varchar(20) DEFAULT NULL,
 `indirizzo` varchar(100) DEFAULT NULL,
 `civico` varchar(50) DEFAULT NULL,
 `cap` varchar(5) DEFAULT NULL,
 `latitude` double NOT NULL DEFAULT '0',
 `longitude` double NOT NULL DEFAULT '0',
 `ind_stati_id` smallint(3) unsigned DEFAULT '110',
 `ind_province_id` int(11) DEFAULT NULL,
 `ind_comuni_id` int(11) DEFAULT NULL,
 `ind_zone_id` int(11) DEFAULT NULL,
 `id_agente` int(11) NOT NULL,
 `id_agenzia` int(11) NOT NULL,
 `flag_storico` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `deleted` tinyint(1) NOT NULL DEFAULT '0',
 `tipo_customer` enum('compra','vende','entrambi','costruttore','contatto','altro') NOT NULL DEFAULT 'altro',
 `rating` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `privacy_flag` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `note` text,
 `azienda_flag` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `data_nascita` date DEFAULT NULL,
 `luogo_nascita` varchar(255) DEFAULT NULL,
 `company_name` varchar(255) DEFAULT NULL,
 `iva_cf` varchar(50) DEFAULT NULL,
 `codice_fiscale` varchar(50) DEFAULT NULL,
 `creator_time` datetime NOT NULL DEFAULT '1970-01-01 01:00:00',
 `last_editor_time` datetime NOT NULL DEFAULT '1970-01-01 01:00:00',
 `external_id_customer` varchar(255) DEFAULT NULL,
 `external_id_richiesta_tipo` enum('R','P','D') DEFAULT NULL,
 `external_id_richiesta` int(11) DEFAULT NULL,
 PRIMARY KEY (`id_customer`),
 UNIQUE KEY `id_agenzia-id_agente-external_id_customer` (`id_agenzia`,`id_agente`,`external_id_customer`),
 KEY `ind_comuni_id` (`ind_comuni_id`),
 KEY `ind_province_id` (`ind_province_id`),
 KEY `id_agente-id_agenzia-id_customer` (`id_agente`,`id_agenzia`,`id_customer`),
 KEY `surname-name` (`surname`,`name`),
 KEY `id_agenzia-id_agente-flag_storico-deleted` (`id_agenzia`,`id_agente`,`flag_storico`,`deleted`),
 KEY `email_email2` (`email`,`email2`),
 KEY `ind_stati_id` (`ind_stati_id`),
 KEY `ind_zone_id` (`ind_zone_id`),
 KEY `id_agenzia-external_id_customer` (`id_agenzia`,`external_id_customer`),
 KEY `rating` (`rating`),
 CONSTRAINT `customers_ibfk_23` FOREIGN KEY (`ind_stati_id`) REFERENCES `ind_stati` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `customers_ibfk_24` FOREIGN KEY (`ind_province_id`) REFERENCES `ind_province` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `customers_ibfk_25` FOREIGN KEY (`id_agente`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `customers_ibfk_26` FOREIGN KEY (`id_agenzia`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `customers_ibfk_28` FOREIGN KEY (`ind_comuni_id`) REFERENCES `ind_comuni` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `customers_ibfk_29` FOREIGN KEY (`ind_zone_id`) REFERENCES `ind_zone` (`id`) ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=10020244 DEFAULT CHARSET=utf8


CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `parent_users_id` int(11) DEFAULT NULL,
 `franchising_id` int(11) DEFAULT NULL,
 `affiliates_id` smallint(5) unsigned DEFAULT NULL,
 `website_type` enum('dinamico','professionale','sito_terzi','non_definito') NOT NULL DEFAULT 'non_definito',
 `flag_website_modulistica` tinyint(4) NOT NULL DEFAULT '0',
 `email` varchar(255) NOT NULL,
 `email_pec` varchar(255) NOT NULL,
 `password` varchar(255) NOT NULL,
 `password_sent` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `show_email` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `newsletter_wanted` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `referrer` varchar(255) DEFAULT NULL,
 `logo_img` varchar(255) DEFAULT NULL,
 `agenzia_img` varchar(255) DEFAULT NULL,
 `watermark_img` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `login_enabled` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `login_expires` datetime DEFAULT NULL,
 `max_ads` int(11) NOT NULL DEFAULT '0',
 `flag_import` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `creation_time` datetime NOT NULL DEFAULT '1970-01-01 01:00:00',
 `contract_price` int(11) DEFAULT NULL,
 `modification_time` datetime DEFAULT NULL,
 `lastlogin_time` datetime DEFAULT NULL,
 `lastlogin_ip` varchar(30) DEFAULT NULL,
 `lastnotification_time` datetime DEFAULT NULL,
 `note` text,
 PRIMARY KEY (`id`),
 UNIQUE KEY `email` (`email`),
 KEY `password_sent` (`password_sent`),
 KEY `creation_time` (`creation_time`),
 KEY `lastlogin_time` (`lastlogin_time`),
 KEY `login_expires` (`login_expires`),
 KEY `login_enabled` (`login_enabled`),
 KEY `password` (`password`),
 KEY `newsletter_wanted` (`newsletter_wanted`),
 KEY `referrer` (`referrer`),
 KEY `parent_users_id` (`parent_users_id`),
 KEY `franchising_id` (`franchising_id`),
 KEY `affiliates_id` (`affiliates_id`),
 KEY `email-login_enabled` (`email`,`login_enabled`),
 KEY `email-password` (`email`,`password`),
 KEY `id-logo_img` (`id`,`logo_img`),
 KEY `rinnovo_automatico` (`rinnovo_automatico`),
 KEY `id` (`id`,`login_enabled`),
 CONSTRAINT `users_ibfk_10` FOREIGN KEY (`parent_users_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `users_ibfk_11` FOREIGN KEY (`franchising_id`) REFERENCES `franchising` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `users_ibfk_12` FOREIGN KEY (`affiliates_id`) REFERENCES `affiliates` (`id`) ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=2760 DEFAULT CHARSET=utf8



CREATE TABLE `ads` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `unique_code` varchar(50) NOT NULL DEFAULT '',
 `agency_code` varchar(255) DEFAULT NULL,
 `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_storico` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `rating` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `logo_img_filename` varchar(255) DEFAULT NULL,
 `allegati_xml` varchar(3500) DEFAULT NULL,
 `planimetrie_xml` varchar(3500) DEFAULT NULL,
 `virtual_tour_panora` varchar(255) DEFAULT NULL,
 `video_tour` varchar(255) DEFAULT NULL,
 `vt360` varchar(255) DEFAULT NULL,
 `mq` int(11) unsigned DEFAULT '0',
 `mq_hidden` int(6) unsigned DEFAULT '0',
 `price` int(11) unsigned DEFAULT '0',
 `price_xml` int(11) unsigned DEFAULT '0',
 `age` varchar(255) DEFAULT NULL,
 `ipe` decimal(6,2) DEFAULT '0.00',
 `abstract` text,
 `seo_title` varchar(100) NOT NULL DEFAULT '',
 `description` text,
 `latitude` double NOT NULL DEFAULT '0',
 `longitude` double NOT NULL DEFAULT '0',
 `latitude_real` double NOT NULL DEFAULT '0',
 `longitude_real` double NOT NULL DEFAULT '0',
 `ind_stati_id` smallint(3) unsigned NOT NULL DEFAULT '110',
 `ind_province_id` int(11) DEFAULT NULL,
 `ind_comuni_id` int(11) DEFAULT NULL,
 `ind_zone_id` int(11) DEFAULT NULL,
 `ind_zone_casa_it_id` int(11) DEFAULT NULL,
 `ind_zone_immobiliare_it_id` int(11) DEFAULT NULL,
 `zona` varchar(255) DEFAULT NULL,
 `indirizzo` varchar(255) DEFAULT NULL,
 `civico` varchar(255) DEFAULT NULL,
 `civico_interno` varchar(50) DEFAULT NULL,
 `indirizzo_xml` varchar(255) DEFAULT NULL,
 `consegna` varchar(255) DEFAULT NULL,
 `spese_condominiali` varchar(255) DEFAULT NULL,
 `unita_abitative` varchar(255) DEFAULT NULL,
 `finiture` text,
 `nord` tinyint(1) unsigned DEFAULT '0',
 `sud` tinyint(1) unsigned DEFAULT '0',
 `ovest` tinyint(1) unsigned DEFAULT '0',
 `est` tinyint(1) unsigned DEFAULT '0',
 `no_nord` tinyint(1) unsigned DEFAULT '0',
 `incarico` enum('condiviso','esclusivo','normale','notizia','censimento') NOT NULL DEFAULT 'normale',
 `incarico_inizio` datetime DEFAULT NULL,
 `incarico_fine` datetime DEFAULT NULL,
 `stato_rogito` enum('non_definito','occupato','libero','affittato') NOT NULL DEFAULT 'non_definito',
 `tipo_affitto` enum('non_definito','3+2','4+4','6+6','9+9','transitorio','concordato','libero','studenti') NOT NULL DEFAULT 'non_definito',
 `tipo_proprieta` enum('non_definito','intera_proprieta','nuda_proprieta','parziale_proprieta','usufrutto','multiproprieta','diritto_di_superficie') NOT NULL DEFAULT 'non_definito',
 `ads_category_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `ads_category_micro_id` tinyint(3) unsigned DEFAULT NULL',
 `flag_vacanza` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_mls` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_share_franchising` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_vetrina` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_website_export` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_website_carosello` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `flag_garantito` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `external_id_immobile` varchar(255) DEFAULT NULL,
 `agent_users_id` int(11) NOT NULL,
 `creator_users_id` int(11) NOT NULL DEFAULT '0',
 `creator_time` datetime NOT NULL DEFAULT '1970-01-01 01:00:00',
 `last_editor_users_id` int(11) NOT NULL DEFAULT '0',
 `last_editor_time` datetime NOT NULL DEFAULT '1970-01-01 01:00:00',
 `note` text,
 `note_franchising` text,
 `edit` tinyint(1) NOT NULL DEFAULT '0',
 `imposte` enum('non_definito','regime_di_iva','imposta_di_registro','esente_iva','mista_iva_registro') NOT NULL DEFAULT 'non_definito',
 `destinazione_uso` enum('non_definito','residenziale','commerciale','artigianale','produttivo','direzionale','agricola') NOT NULL DEFAULT 'non_definito',
 `immobile_reddito` tinyint(1) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `unique_code` (`unique_code`),
 KEY `last_editor_users_id` (`last_editor_users_id`),
 KEY `ads_category_id` (`ads_category_id`),
 KEY `zona` (`zona`),
 KEY `logo_img_filename` (`logo_img_filename`),
 KEY `price` (`price`),
 KEY `agency_code` (`agency_code`),
 KEY `flag_storico` (`flag_storico`),
 KEY `ind_zone_immobiliare_it_id` (`ind_zone_immobiliare_it_id`),
 KEY `creator_users_id-deleted-incarico-last_editor_time` (`creator_users_id`,`deleted`,`incarico`,`last_editor_time`),
 KEY `creator_users_id-deleted-flag_storico` (`creator_users_id`,`deleted`,`flag_storico`),
 KEY `flag_website_export` (`flag_website_export`,`flag_website_carosello`),
 KEY `ind_stati_id` (`ind_stati_id`,`creator_users_id`),
 KEY `mq` (`mq`),
 KEY `external_id_immobile` (`external_id_immobile`),
 KEY `price_xml` (`price_xml`),
 KEY `stato_rogito` (`stato_rogito`),
 KEY `ind_comuni_id_2` (`ind_comuni_id`),
 KEY `ind_zone_casa_it_id` (`ind_zone_casa_it_id`),
 KEY `incarico-deleted-flag_storico` (`agent_users_id`,`incarico`,`deleted`,`flag_storico`),
 KEY `agent_users_id-id` (`agent_users_id`,`id`),
 KEY `ind_comuni_id-creator_users-id` (`ind_comuni_id`,`creator_users_id`,`flag_mls`),
 KEY `ind_zone_id` (`ind_zone_id`,`creator_users_id`),
 KEY `flag_share_franchising` (`flag_share_franchising`),
 KEY `ind_province_id` (`ind_province_id`),
 KEY `ads_category_micro_id` (`ads_category_micro_id`),
 KEY `civico` (`civico`),
 KEY `rating` (`rating`),
 KEY `creator_users_id-deleted-flag_mls` (`creator_users_id`,`deleted`,`flag_mls`) USING BTREE,
 CONSTRAINT `ads_ibfk_116` FOREIGN KEY (`ind_stati_id`) REFERENCES `ind_stati` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `ads_ibfk_117` FOREIGN KEY (`ind_province_id`) REFERENCES `ind_province` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `ads_ibfk_118` FOREIGN KEY (`ind_comuni_id`) REFERENCES `ind_comuni` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `ads_ibfk_119` FOREIGN KEY (`ind_zone_id`) REFERENCES `ind_zone` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `ads_ibfk_120` FOREIGN KEY (`ads_category_id`) REFERENCES `ads_category` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `ads_ibfk_121` FOREIGN KEY (`ads_category_micro_id`) REFERENCES `ads_category_micro` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `ads_ibfk_123` FOREIGN KEY (`creator_users_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `ads_ibfk_124` FOREIGN KEY (`last_editor_users_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `ads_ibfk_125` FOREIGN KEY (`agent_users_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4095242 DEFAULT CHARSET=utf8

Anyone could give me some advices please?

Best Answer

(Too long for a Comment)

Does that even work??? I see LEFT JOIN ads twice, but with no alias to distinguish them??

OR is a performance killer, especially across multiple tables. UNION may help.

LIKE with a leading wildcard is a performance killer. Consider FULLTEXT.

JOIN..ON should say how things are related. users.login_enabled = 1 smells like filtering not relationship.

The EXPLAIN says that the index agenzia was used -- but there is no such index??

Related Question