Hi
Thanks for answering to all of you
There is no index on the order by column. Adding it does not improve performance.
Here it is the output of explain:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE _fatture_passive NULL index PRIMARY PRIMARY 4 NULL 175091 100.00 "Using temporary; Using filesort"
1 SIMPLE relations NULL ref record,rtf rtf 4 kotik._fatture_passive.id 1 100.00 "Using index"
1 SIMPLE meta NULL eq_ref PRIMARY PRIMARY 4 kotik._fatture_passive.id 1 100.00 NULL
1 SIMPLE _0004_ NULL ref record,table,field,rtf rtf 308 kotik._fatture_passive.id,const,const 1 100.00 NULL
1 SIMPLE _0005_ NULL eq_ref PRIMARY PRIMARY 4 kotik._0004_.value 1 100.00 "Using where; Not exists; Using index"
1 SIMPLE _0006_ NULL ref record,table,field,rtf rtf 308 kotik._fatture_passive.id,const,const 1 100.00 NULL
1 SIMPLE _0007_ NULL eq_ref PRIMARY PRIMARY 4 kotik._0006_.value 1 100.00 "Using where; Not exists; Using index"
1 SIMPLE _0008_ NULL ref record,table,field,rtf rtf 308 kotik._fatture_passive.id,const,const 1 100.00 NULL
1 SIMPLE _0009_ NULL eq_ref PRIMARY PRIMARY 4 kotik._0008_.value 1 100.00 "Using where; Not exists; Using index"
And the tables definitions are below:
CREATE TABLE `_fatture_passive` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`id_sdi` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`payload` longtext COLLATE utf8_unicode_ci,
`amministratore` tinyint(4) DEFAULT NULL,
`condominio` tinyint(4) DEFAULT NULL,
`numero` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`data` date DEFAULT NULL,
`tipo_documento` tinyint(4) DEFAULT NULL,
`totale_documento` float DEFAULT NULL,
`ritenuta` float DEFAULT NULL,
`uniqid` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`xml` longtext COLLATE utf8_unicode_ci,
`fornitore` json DEFAULT NULL,
`filename` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`ultima_elaborazione` date DEFAULT NULL,
`debug_code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`contabilizzata` datetime DEFAULT NULL,
`esportata` datetime DEFAULT NULL,
`master_code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`notificata` datetime DEFAULT NULL,
`blacklist` tinyint(4) DEFAULT NULL,
`data_ricezione` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=300358 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `relations` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`record` int(10) unsigned NOT NULL,
`table` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`field` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`source` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `record` (`record`),
KEY `table` (`table`),
KEY `field` (`field`),
KEY `rtf` (`record`,`table`,`field`)
) ENGINE=InnoDB AUTO_INCREMENT=768196 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `meta` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`table` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`date_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`user_created` int(11) DEFAULT NULL,
`date_modified` timestamp NULL DEFAULT NULL,
`user_modified` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=300358 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `_amministratori` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`id_multidialogo` int(11) DEFAULT NULL,
`denominazione` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`codice_fiscale` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
`partita_iva` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
`stato` tinyint(4) DEFAULT NULL,
`email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`pec` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`promo_code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`accettazione` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`sdd` tinyint(1) DEFAULT NULL,
`scadenza` datetime DEFAULT NULL,
`crediti` int(11) DEFAULT NULL,
`debug` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_multidialogo` (`id_multidialogo`)
) ENGINE=InnoDB AUTO_INCREMENT=299108 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `_condomini` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`id_multidialogo` int(11) DEFAULT NULL,
`amministratore` tinyint(4) DEFAULT NULL,
`codice_fiscale` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`denominazione` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`stato` tinyint(4) DEFAULT NULL,
`sdd` tinyint(1) DEFAULT NULL,
`scadenza` datetime DEFAULT NULL,
`bonifico` json DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_multidialogo` (`id_multidialogo`)
) ENGINE=InnoDB AUTO_INCREMENT=299993 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `_blacklist` (
`id` int(11) unsigned NOT NULL,
`valore` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
I also repeat the query beatified:
SELECT
`_fatture_passive`.`id` AS `id`,
NULL AS `amministratore`,
NULL AS `blacklist`,
NULL AS `condominio`,
`_fatture_passive`.`contabilizzata` AS `contabilizzata`,
`_fatture_passive`.`data` AS `data`,
`_fatture_passive`.`data_ricezione` AS `data_ricezione`,
`_fatture_passive`.`debug_code` AS `debug_code`,
`_fatture_passive`.`esportata` AS `esportata`,
`_fatture_passive`.`filename` AS `filename`,
`_fatture_passive`.`fornitore` AS `fornitore`,
`_fatture_passive`.`id_sdi` AS `id_sdi`,
`_fatture_passive`.`master_code` AS `master_code`,
`_fatture_passive`.`notificata` AS `notificata`,
`_fatture_passive`.`numero` AS `numero`,
`_fatture_passive`.`payload` AS `payload`,
`_fatture_passive`.`ritenuta` AS `ritenuta`,
NULL AS `tipo_documento`,
`_fatture_passive`.`totale_documento` AS `totale_documento`,
`_fatture_passive`.`ultima_elaborazione` AS `ultima_elaborazione`,
`_fatture_passive`.`uniqid` AS `uniqid`,
`_fatture_passive`.`xml` AS `xml`,
`meta`.`table` AS `table`,
`meta`.`date_created` AS `date_created`,
`meta`.`user_created` AS `user_created`,
`meta`.`date_modified` AS `date_modified`,
`meta`.`user_modified` AS `user_modified`
FROM
`_fatture_passive`
LEFT JOIN
`relations` AS `relations` ON `relations`.`record` = `_fatture_passive`.`id`
LEFT JOIN
`meta` AS `meta` ON `meta`.`id` = `_fatture_passive`.`id`
LEFT JOIN
`relations` AS `_0004_` ON `_0004_`.`record` = `_fatture_passive`.`id`
AND `_0004_`.`table` = '_fatture_passive'
AND `_0004_`.`field` = 'amministratore'
LEFT JOIN
`_amministratori` AS `_0005_` ON `_0005_`.`id` = `_0004_`.`value`
LEFT JOIN
`relations` AS `_0006_` ON `_0006_`.`record` = `_fatture_passive`.`id`
AND `_0006_`.`table` = '_fatture_passive'
AND `_0006_`.`field` = 'condominio'
LEFT JOIN
`_condomini` AS `_0007_` ON `_0007_`.`id` = `_0006_`.`value`
LEFT JOIN
`relations` AS `_0008_` ON `_0008_`.`record` = `_fatture_passive`.`id`
AND `_0008_`.`table` = '_fatture_passive'
AND `_0008_`.`field` = 'blacklist'
LEFT JOIN
`_blacklist` AS `_0009_` ON `_0009_`.`id` = `_0008_`.`value`
WHERE
((`_0005_`.`id` IS NULL)
AND (`_0007_`.`id` IS NULL)
AND (`_0009_`.`id` IS NULL))
GROUP BY `_fatture_passive`.`id`
ORDER BY `_fatture_passive`.`ultima_elaborazione` ASC
LIMIT 200;
Thanks a lot again
Ciao
Nicola