CODEWITHSUNDEEP
mysqlselectindexing
Leo Nogueira
Leo Nogueira

Reputation: 108

Wrong result with 2 indexes

id  company  supplier
1   10       105
2   10       102
3   10       105
4   10       112
5   10       105

Lets take the above table as example.

Using company and supplier as indexes.

Using SELECT * FROM table WHERE company = 10; -> returns 5 rows. OK

Using SELECT * from table WHERE supplier = 105; -> returns 3 rows. OK

Using SELECT * from table WHERE company = 10 AND supplier = 105; -> returns only 1 row. PROBLEM HERE.

table is using INNODB format.

I already used OPTIMIZE TABLE.

Any ideas about this behavior?

As requested. Original table. Problems with fields "empresa" and "fornecedor".

CREATE TABLE `contas_lancadas` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_vinculo` int(11) unsigned NOT NULL DEFAULT '0',
  `tipo_vinculo` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'P' COMMENT 'P - Pedido, OS - Ordem de Serviço',
  `numero` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `data_doc` date DEFAULT NULL,
  `moeda` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `empresa` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'ID da empresa',
  `conta` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'ID do Plano de Conta',
  `fornecedor` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'ID dos Favorecidos (Cliente e Fornecedor)',
  `conta_corrente` int(6) unsigned NOT NULL DEFAULT '0' COMMENT 'ID da conta corrente',
  `vencto` date NOT NULL DEFAULT '0000-00-00',
  `pago` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0 - Não-Pago - 1 - Pago.',
  `valor` decimal(10,2) NOT NULL DEFAULT '0.00',
  `descricao` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `replicar` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '0 - Não possui réplica, 1 - È a conta origem de uma série replicada, N - ID da primeira conta',
  `condicao` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0 - Previsto - 1 - Real',
  `data_pgto` date DEFAULT '0000-00-00',
  `juros` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'juros pagos da conta',
  `multa` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'multa paga da conta',
  `desconto` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'desconto recebido da conta',
  `data_cadastro` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `id_caderneta` int(11) DEFAULT '0',
  `vencto_original` date DEFAULT '0000-00-00' COMMENT 'Data de vencimento original (data do cadastro)',
  PRIMARY KEY (`id`),
  KEY `id_pedido` (`id_vinculo`),
  KEY `numero` (`numero`),
  KEY `conta` (`conta`),
  KEY `conta_corrente` (`conta_corrente`),
  KEY `fornecedor` (`fornecedor`),
  KEY `empresa` (`empresa`)
) ENGINE=InnoDB AUTO_INCREMENT=1221384 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I tried to delete and recreate the indexes. Without the indexes works fine. With them... same problem.

Upvotes: 2

Views: 61

Answers (1)

Leo Nogueira
Leo Nogueira

Reputation: 108

Problem solved.

Updated the MySQL to version 5.6.19 (was 5.5.something).

The issue was a partitioned table with multiple indexes. More information here link

Thanks for the help.

Upvotes: 1

Related Questions