3

I'm migrating my database more to my surprise I had a drop in the performance search, I already checked all the indexes of the tables and they are the same

Server New MariaDB 10.6.8 CentOS 7 8 VCpu 2.8 16 RAM

enter image description here

enter image description here

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
def shop nfe 0 shop PRIMARY 1 fk_venda A 389502 BTREE NO
def shop nfe 0 shop fk_venda 1 fk_venda A 194751 BTREE NO
def shop nfe 1 shop numero 1 numero A 194751 BTREE NO
def shop vendas 0 shop PRIMARY 1 id_venda A 120978 BTREE NO
def shop vendas 0 shop codigo 1 codigo A 120978 YES BTREE NO
def shop vendas 0 shop guid 1 guid A 120978 YES BTREE NO
def shop vendas 0 shop erp 1 fk_loja A 2 YES BTREE NO
def shop vendas 0 shop erp 2 erp A 15122 YES BTREE NO
def shop vendas 1 shop vendas 1 id_venda A 120978 BTREE NO
def shop vendas 1 shop vendas 2 fk_loja A 120978 YES BTREE NO
def shop vendas 1 shop vendas 3 fk_cliente A 120978 YES BTREE NO
def shop vendas 1 shop vendas 4 fk_frete A 120978 YES BTREE NO
def shop vendas 1 shop vendas 5 fk_status A 120978 YES BTREE NO
def shop vendas 1 shop fk_loja 1 fk_loja A 2 YES BTREE NO
def shop vendas 1 shop fk_cliente 1 fk_cliente A 120978 YES BTREE NO
def shop vendas 1 shop fk_frete 1 fk_frete A 72 YES BTREE NO
def shop vendas 1 shop fk_status 1 fk_status A 26 YES BTREE NO
def shop vendas 1 shop fk_cupom 1 fk_cupom A 2 YES BTREE NO
def shop vendas 1 shop fk_estado 1 fk_estado A 52 YES BTREE NO
def shop vendas 1 shop fk_pais 1 fk_pais A 2 YES BTREE NO
def shop vendas 1 shop fk_pagamento 1 fk_pagamento A 38 YES BTREE NO
def shop vendas 1 shop phpsessid 1 phpsessid A 120978 YES BTREE NO
def shop vendas 1 shop notificar 1 notificar A 4 YES BTREE NO
def shop vendas 1 shop vendas_ibfk_9 1 fk_marketplace A 48 YES BTREE NO
def shop vendas 1 shop data_compra 1 data_compra A 120978 YES BTREE NO
def shop vendas 1 shop tags 1 tags A 8 YES BTREE NO
def shop vendas 1 shop coupon_id 1 coupon_id A 32 YES BTREE NO
def shop vendas 1 shop created_at 1 created_at A 120978 BTREE NO 
CREATE TABLE `vendas` (
  `guid` varchar(40) DEFAULT uuid(),
  `id_venda` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `codigo` varchar(25) DEFAULT NULL,
  `erp` varchar(20) DEFAULT NULL,
  `fk_loja` int(11) DEFAULT NULL,
  `fk_cliente` int(11) DEFAULT NULL,
  `fk_frete` int(11) DEFAULT NULL,
  `fk_status` int(11) DEFAULT NULL,
  `fk_cupom` int(11) DEFAULT NULL,
  `shipment_id` int(11) DEFAULT NULL,
  `phpsessid` varchar(32) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT 0.00,
  `desconto` decimal(10,2) DEFAULT 0.00,
  `abatimento` decimal(10,2) DEFAULT 0.00,
  `desconto_boleto` decimal(12,4) DEFAULT 0.0000,
  `desconto_boleto_rede` decimal(12,4) DEFAULT 0.0000,
  `desconto_cartao` decimal(12,4) DEFAULT 0.0000,
  `acrescimo` decimal(10,2) DEFAULT 0.00,
  `tarifa` decimal(10,2) DEFAULT 0.00,
  `juros` decimal(10,2) DEFAULT 0.00,
  `data_compra` datetime DEFAULT '0000-00-00 00:00:00',
  `data_pagamento` datetime DEFAULT '0000-00-00 00:00:00',
  `data_preparo` datetime DEFAULT '0000-00-00 00:00:00',
  `data_envio` datetime DEFAULT '0000-00-00 00:00:00',
  `data_estimada` datetime DEFAULT NULL,
  `data_entrega` datetime DEFAULT '0000-00-00 00:00:00',
  `data_cancelamento` datetime DEFAULT '0000-00-00 00:00:00',
  `numero_objeto` varchar(100) DEFAULT '',
  `comentario` text DEFAULT NULL,
  `tipo_residencia` char(1) DEFAULT NULL,
  `endereco` varchar(100) DEFAULT NULL,
  `numero` varchar(50) DEFAULT NULL,
  `bairro` varchar(50) DEFAULT '',
  `complemento` varchar(100) DEFAULT '',
  `cidade` varchar(50) DEFAULT NULL,
  `fk_estado` int(11) DEFAULT NULL,
  `fk_pais` int(11) DEFAULT NULL,
  `provincia` varchar(100) DEFAULT NULL,
  `referencia` text DEFAULT NULL,
  `entregar_para` varchar(100) DEFAULT NULL,
  `cep` char(9) DEFAULT NULL,
  `fk_pagamento` int(11) DEFAULT 0,
  `parcelas` char(2) DEFAULT NULL,
  `fone1` varchar(15) DEFAULT NULL,
  `fone2` varchar(15) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `cpf_cnpj` varchar(20) DEFAULT NULL,
  `prazo` int(2) DEFAULT 0,
  `ultima_alteracao` datetime DEFAULT NULL,
  `integrada` int(1) DEFAULT 0,
  `notificar` int(1) DEFAULT NULL,
  `host` varchar(100) DEFAULT NULL,
  `fk_marketplace` int(11) DEFAULT 1,
  `dynamic_cols` blob DEFAULT NULL,
  `tags` varchar(250) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `whatsapp` int(1) DEFAULT 0,
  `coupon_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_venda`),
  UNIQUE KEY `codigo` (`codigo`) USING BTREE,
  UNIQUE KEY `guid` (`guid`),
  UNIQUE KEY `erp` (`fk_loja`,`erp`) USING BTREE,
  KEY `vendas` (`id_venda`,`fk_loja`,`fk_cliente`,`fk_frete`,`fk_status`),
  KEY `fk_loja` (`fk_loja`),
  KEY `fk_cliente` (`fk_cliente`),
  KEY `fk_frete` (`fk_frete`),
  KEY `fk_status` (`fk_status`),
  KEY `fk_cupom` (`fk_cupom`),
  KEY `fk_estado` (`fk_estado`),
  KEY `fk_pais` (`fk_pais`),
  KEY `fk_pagamento` (`fk_pagamento`),
  KEY `phpsessid` (`phpsessid`),
  KEY `notificar` (`notificar`),
  KEY `vendas_ibfk_9` (`fk_marketplace`) USING BTREE,
  KEY `data_compra` (`data_compra`),
  KEY `tags` (`tags`),
  KEY `coupon_id` (`coupon_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `vendas_ibfk_1` FOREIGN KEY (`fk_loja`) REFERENCES `lojas` (`id_loja`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_10` FOREIGN KEY (`coupon_id`) REFERENCES `discount_coupon` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_2` FOREIGN KEY (`fk_cliente`) REFERENCES `clientes` (`id_cliente`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_3` FOREIGN KEY (`fk_frete`) REFERENCES `fretes` (`id_frete`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_4` FOREIGN KEY (`fk_status`) REFERENCES `status` (`id_status`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_5` FOREIGN KEY (`fk_cupom`) REFERENCES `cupom` (`id_cupom`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_6` FOREIGN KEY (`fk_estado`) REFERENCES `estados` (`id_estado`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_7` FOREIGN KEY (`fk_pais`) REFERENCES `paises` (`id_pais`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_8` FOREIGN KEY (`fk_pagamento`) REFERENCES `pagamentos` (`id_pagamento`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_9` FOREIGN KEY (`fk_marketplace`) REFERENCES `marketplace` (`id_marketplace`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=693460 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `nfe` (
  `fk_venda` int(11) NOT NULL,
  `numero` int(11) NOT NULL,
  `serie` int(2) NOT NULL,
  `chave` varchar(60) DEFAULT NULL,
  `emissao` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `xml` longtext DEFAULT NULL,
  `arquivo` blob DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`fk_venda`),
  UNIQUE KEY `fk_venda` (`fk_venda`) USING BTREE,
  KEY `numero` (`numero`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Server Old MariaDB 10.5.16 CentOS 7 8 VCpu 2.2 16 RAM

CREATE TABLE `vendas` (
  `guid` varchar(40) DEFAULT uuid(),
  `id_venda` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `codigo` varchar(25) DEFAULT NULL,
  `erp` varchar(20) DEFAULT NULL,
  `fk_loja` int(11) DEFAULT NULL,
  `fk_cliente` int(11) DEFAULT NULL,
  `fk_frete` int(11) DEFAULT NULL,
  `fk_status` int(11) DEFAULT NULL,
  `fk_cupom` int(11) DEFAULT NULL,
  `shipment_id` int(11) DEFAULT NULL,
  `phpsessid` varchar(32) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT 0.00,
  `desconto` decimal(10,2) DEFAULT 0.00,
  `abatimento` decimal(10,2) DEFAULT 0.00,
  `desconto_boleto` decimal(12,4) DEFAULT 0.0000,
  `desconto_boleto_rede` decimal(12,4) DEFAULT 0.0000,
  `desconto_cartao` decimal(12,4) DEFAULT 0.0000,
  `acrescimo` decimal(10,2) DEFAULT 0.00,
  `tarifa` decimal(10,2) DEFAULT 0.00,
  `juros` decimal(10,2) DEFAULT 0.00,
  `data_compra` datetime DEFAULT '0000-00-00 00:00:00',
  `data_pagamento` datetime DEFAULT '0000-00-00 00:00:00',
  `data_preparo` datetime DEFAULT '0000-00-00 00:00:00',
  `data_envio` datetime DEFAULT '0000-00-00 00:00:00',
  `data_estimada` datetime DEFAULT NULL,
  `data_entrega` datetime DEFAULT '0000-00-00 00:00:00',
  `data_cancelamento` datetime DEFAULT '0000-00-00 00:00:00',
  `numero_objeto` varchar(100) DEFAULT '',
  `comentario` text DEFAULT NULL,
  `tipo_residencia` char(1) DEFAULT NULL,
  `endereco` varchar(100) DEFAULT NULL,
  `numero` varchar(50) DEFAULT NULL,
  `bairro` varchar(50) DEFAULT '',
  `complemento` varchar(100) DEFAULT '',
  `cidade` varchar(50) DEFAULT NULL,
  `fk_estado` int(11) DEFAULT NULL,
  `fk_pais` int(11) DEFAULT NULL,
  `provincia` varchar(100) DEFAULT NULL,
  `referencia` text DEFAULT NULL,
  `entregar_para` varchar(100) DEFAULT NULL,
  `cep` char(9) DEFAULT NULL,
  `fk_pagamento` int(11) DEFAULT 0,
  `parcelas` char(2) DEFAULT NULL,
  `fone1` varchar(15) DEFAULT NULL,
  `fone2` varchar(15) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `cpf_cnpj` varchar(20) DEFAULT NULL,
  `prazo` int(2) DEFAULT 0,
  `ultima_alteracao` datetime DEFAULT NULL,
  `integrada` int(1) DEFAULT 0,
  `notificar` int(1) DEFAULT NULL,
  `host` varchar(100) DEFAULT NULL,
  `fk_marketplace` int(11) DEFAULT 1,
  `dynamic_cols` blob DEFAULT NULL,
  `tags` varchar(250) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `whatsapp` int(1) DEFAULT 0,
  `coupon_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_venda`),
  UNIQUE KEY `codigo` (`codigo`) USING BTREE,
  UNIQUE KEY `guid` (`guid`),
  UNIQUE KEY `erp` (`fk_loja`,`erp`) USING BTREE,
  KEY `vendas` (`id_venda`,`fk_loja`,`fk_cliente`,`fk_frete`,`fk_status`),
  KEY `fk_cliente` (`fk_cliente`),
  KEY `fk_frete` (`fk_frete`),
  KEY `fk_status` (`fk_status`),
  KEY `fk_cupom` (`fk_cupom`),
  KEY `fk_estado` (`fk_estado`),
  KEY `fk_pais` (`fk_pais`),
  KEY `fk_pagamento` (`fk_pagamento`),
  KEY `phpsessid` (`phpsessid`),
  KEY `notificar` (`notificar`),
  KEY `vendas_ibfk_9` (`fk_marketplace`) USING BTREE,
  KEY `tags` (`tags`),
  KEY `coupon_id` (`coupon_id`),
  KEY `created_at` (`created_at`),
  KEY `data_compra` (`data_compra`),
  CONSTRAINT `vendas_ibfk_10` FOREIGN KEY (`coupon_id`) REFERENCES `discount_coupon` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_11` FOREIGN KEY (`fk_loja`) REFERENCES `lojas` (`id_loja`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_2` FOREIGN KEY (`fk_cliente`) REFERENCES `clientes` (`id_cliente`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_3` FOREIGN KEY (`fk_frete`) REFERENCES `fretes` (`id_frete`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_4` FOREIGN KEY (`fk_status`) REFERENCES `status` (`id_status`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_5` FOREIGN KEY (`fk_cupom`) REFERENCES `cupom` (`id_cupom`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_6` FOREIGN KEY (`fk_estado`) REFERENCES `estados` (`id_estado`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_7` FOREIGN KEY (`fk_pais`) REFERENCES `paises` (`id_pais`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_8` FOREIGN KEY (`fk_pagamento`) REFERENCES `pagamentos` (`id_pagamento`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_9` FOREIGN KEY (`fk_marketplace`) REFERENCES `marketplace` (`id_marketplace`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=693701 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `nfe` (
  `fk_venda` int(11) NOT NULL,
  `numero` int(11) NOT NULL,
  `serie` int(2) NOT NULL,
  `chave` varchar(60) DEFAULT NULL,
  `emissao` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `xml` longtext DEFAULT NULL,
  `arquivo` blob DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`fk_venda`),
  UNIQUE KEY `fk_venda` (`fk_venda`) USING BTREE,
  KEY `numero` (`numero`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

enter image description here enter image description here

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT
def shop vendas 0 shop PRIMARY 1 id_venda A 120554 BTREE
def shop vendas 0 shop codigo 1 codigo A 120554 YES BTREE
def shop vendas 0 shop guid 1 guid A 120554 YES BTREE
def shop vendas 0 shop erp 1 fk_loja A 2 YES BTREE
def shop vendas 0 shop erp 2 erp A 17222 YES BTREE
def shop vendas 1 shop vendas 1 id_venda A 120554 BTREE
def shop vendas 1 shop vendas 2 fk_loja A 120554 YES BTREE
def shop vendas 1 shop vendas 3 fk_cliente A 120554 YES BTREE
def shop vendas 1 shop vendas 4 fk_frete A 120554 YES BTREE
def shop vendas 1 shop vendas 5 fk_status A 120554 YES BTREE
def shop vendas 1 shop fk_cliente 1 fk_cliente A 120554 YES BTREE
def shop vendas 1 shop fk_frete 1 fk_frete A 74 YES BTREE
def shop vendas 1 shop fk_status 1 fk_status A 24 YES BTREE
def shop vendas 1 shop fk_cupom 1 fk_cupom A 2 YES BTREE
def shop vendas 1 shop fk_estado 1 fk_estado A 52 YES BTREE
def shop vendas 1 shop fk_pais 1 fk_pais A 2 YES BTREE
def shop vendas 1 shop fk_pagamento 1 fk_pagamento A 36 YES BTREE
def shop vendas 1 shop phpsessid 1 phpsessid A 120554 YES BTREE
def shop vendas 1 shop notificar 1 notificar A 4 YES BTREE
def shop vendas 1 shop vendas_ibfk_9 1 fk_marketplace A 50 YES BTREE
def shop vendas 1 shop tags 1 tags A 8 YES BTREE
def shop vendas 1 shop coupon_id 1 coupon_id A 30 YES BTREE
def shop vendas 1 shop created_at 1 created_at A 120554 BTREE
def shop vendas 1 shop data_compra 1 data_compra A 120554 YES BTREE
def shop nfe 0 shop PRIMARY 1 fk_venda A 196585 BTREE
def shop nfe 0 shop fk_venda 1 fk_venda A 196585 BTREE
def shop nfe 1 shop numero 1 numero A 196585 BTREE
Improve this question
0

2 Answers 2

Reset to default
2

Three possibilities that I can think of:

  • vendas: Replace INDEX(fk_loga) with INDEX(fk_loja, codigo). I would expect a better EXPLAIN.

  • I assume that status has PRIMARY KEY(status). The JOIN to status seems to be useless. Will you get the same answer if you remove it? That will also speed up the query.

  • SHOW VARIABLES LIKE "query_cache%"; on each server. There may have been a change there.

Improve this answer
2
  • the first suggestion does not take effect, the second suggestion to remove the join, I would have the same answer, I need them, in this case I left the sql simpler just to exemplify that the join in the tables is screwing with the query, other joins what I do with this table is slow on this new server equal values on both servers query_cache_limit=1048576 query_cache_min_res_unit=4096 query_cache_size=1048576 query_cache_strip_comments=OFF query_cache_type=OFF query_cache_wlock_invalidate=OFF
    – Elvis Reis
    Commented Jun 23, 2022 at 2:59
  • @ElvisReis - Please show us the full query, there may be subtle things lost in the simplification.
    – Rick James
    Commented Jun 23, 2022 at 3:04
1

enter image description here enter image description here

after adding an index to the status.tag enter image description here enter image description here

it makes no sense for me to exceed my index bank in this case the status table is small with less than 30 records

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.