Eu tenho a seguinte tabela:
CREATE TABLE `matches` (
`id` char(32) NOT NULL,
`borrower_id` int(10) unsigned NOT NULL,
`product_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`lender_id` int(10) unsigned NOT NULL,
`classification_id` int(10) unsigned DEFAULT NULL,
`status` enum('accepted','partial','potential','rejected') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`classification` varchar(255) DEFAULT NULL,
`category` varchar(255) DEFAULT NULL,
`lender` varchar(255) DEFAULT NULL,
`product` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `borrower_id` (`borrower_id`,`product_id`),
KEY `status` (`status`),
KEY `created_timestamp` (`created`),
KEY `borrower_id_2` (`borrower_id`,`product_id`,`created`),
KEY `borrower-product-classification` (`borrower_id`,`product_id`,`classification_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Quando executo EXPLAIN SELECT COUNT(*) FROM bi.matches WHERE created >= '2016-09-10';, obtenho os seguintes resultados:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|----|-------------|--------|------|-------|-------------------|------------------|---------|------|----------|--------------------------|
1| SIMPLE | matches | range | created_timestamp | created_timestamp | 4 | NULL | 13288480 | Using where; Using index |
Mas quando eu executo esta consulta:
EXPLAIN SELECT COUNT(*)
FROM bi.matches m
INNER JOIN bi.matches m1
ON m.borrower_id = m1.borrower_id
AND m.product_id = m1.product_id
AND m.created > m1.created
LEFT OUTER JOIN bi.matches m2
ON m1.borrower_id = m2.borrower_id
AND m1.product_id = m2.product_id
AND m.created > m2.created
AND (m1.created < m2.created OR (m1.created = m2.created AND m1.id < m2.id))
WHERE m.created >= '2016-09-10'
AND m2.id IS NULL
AND m.status = m1.status;
Eu recebo a seguinte explicação:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|==========================================================================|
| 1 | SIMPLE | m | ALL | borrower_id,status,created_timestamp,borrower_id_2,borrower-product-classification | NULL | NULL | NULL | 75151052 | Using where |
| 1 | SIMPLE | m1 | ref | borrower_id,status,created_timestamp,borrower_id_2,borrower-product-classification | borrower-product-classification | 8 | bi.m.borrower_id,bi.m.product_id | 3 | Using where |
| 1 | SIMPLE | m2 | ref | PRIMARY,borrower_id,created_timestamp,borrower_id_2,borrower-product-classification | borrower_id_2 | 8 | bi.m.borrower_id,bi.m.product_id | 4 | Using where; Not exists; Using index |
Por que o MySQL usaria created_timestampna consulta mais básica, mas não quando as junções estão envolvidas?
Também aqui está a saída deSHOW INDEXES FROM bi.matches
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|=========================================================================|
| matches | 0 | PRIMARY | 1 | id | A | 75151052 | NULL | NULL | | BTREE | | |
| matches | 1 | borrower_id | 1 | borrower_id | A | 434399 | NULL | NULL | | BTREE | | |
| matches | 1 | borrower_id | 2 | product_id | A | 18787763 | NULL | NULL | | BTREE | | |
| matches | 1 | status | 1 | status | A | 2232 | NULL | NULL | | BTREE | | |
| matches | 1 | created_timestamp | 1 | created | A | 37075 | NULL | NULL | | BTREE | | |
| matches | 1 | borrower_id_2 | 1 | borrower_id | A | 521882 | NULL | NULL | | BTREE | | |
| matches | 1 | borrower_id_2 | 2 | product_id | A | 18787763 | NULL | NULL | | BTREE | | |
| matches | 1 | borrower_id_2 | 3 | created | A | 75151052 | NULL | NULL | | BTREE | | |
| matches | 1 | borrower-product-classification | 1 | borrower_id | A | 478669 | NULL | NULL | | BTREE | | |
| matches | 1 | borrower-product-classification | 2 | product_id | A | 25050350 | NULL | NULL | | BTREE | | |
| matches | 1 | borrower-product-classification | 3 | classification_id | A | 18787763 | NULL | NULL | YES | BTREE | | |
