MySQL Forums
Forum List  »  Performance

Re: Bad order by performance (5.7.12 under Aurora)
Posted by: Nicola Farina
Date: November 26, 2019 03:29PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Bad order by performance (5.7.12 under Aurora)
238
November 26, 2019 03:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.