0

I have the following table:

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;

When I run EXPLAIN SELECT COUNT(*) FROM bi.matches WHERE created >= '2016-09-10';, I get the following results:

| 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 |

But when I run this query:

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;

I get the following explain:

| 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 |

Why would MySQL use created_timestamp in the more basic query but not when the joins are involved?

Also here is the output from SHOW 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      |         |               |
  • Run this please: EXPLAIN SELECT * FROM bi.matches WHERE created >= '2016-09-10'; – Jehad Keriaki Sep 15 '16 at 17:26
  • @JehadKeriaki Interesting! This too fails to use the created_timestamp index. – Noah Goodrich Sep 15 '16 at 17:29
  • Ok, it is then related to both: cardinality and data distribution. The reason it could use the index in your first query, because it is enough to count the not null values of created field. Try ti change the value to a very recent date, and this index should be used by the query. – Jehad Keriaki Sep 15 '16 at 17:38
  • @JehadKeriaki Are there ANY solutions to make querying older timestamp faster? Seems like partitioning should in theory do the trick. – Noah Goodrich Sep 15 '16 at 17:47
  • Partitioning would not solve the issue. Let's first understand the problem: If total number of rows that satisfy your where condition is more than certain number, let's say more than 50%, of total rows in the table, it becomes not worth it to use the index, and MySQL would decide not to use it. A better way is to use small date ranges. i.e. WHERE created BETWEEN '2016-09-10' AND '2016-09-11'. Use even smaller ranges if the index was not used. – Jehad Keriaki Sep 15 '16 at 17:58
1

So, here is the conclusion out of the comments:

Running this: EXPLAIN SELECT * FROM bi.matches WHERE created >= '2016-09-10'; showed that the index is not being used, while in the original select count(*)... query the index was used. The reason is that it is enough to count the not null values of created field.

It more probable that the majority of the rows do satisfy the where condition on date, so the optimizer decides that it is not worth it to use the index. Instead, run a full scan.

For the index to be used, smaller date range would help. i.e. WHERE created BETWEEN '2016-09-10' AND '2016-09-11'. Use even smaller ranges if the index was not used.

| improve this answer | |
  • The cutoff is typically 10%-30%. Rarely will it use the index if more than 30% of the table seems to be needed. – Rick James Sep 16 '16 at 17:46
  • COUNT(*) counts rows; COUNT(created) would count non-null occurrences of created. SELECT * must fetch the other columns (null or not). – Rick James Sep 16 '16 at 17:47
0

Since your 'real' question is about performance, I will ignore the "won't use that index" question and..

KEY `status` (`status`),  -- Drop (index on a flag is usually ignored)
KEY `borrower_id` (`borrower_id`,`product_id`),  -- Redundant, drop it.
KEY `borrower_id_2` (`borrower_id`,`product_id`,`created`),  -- good for LEFT JOIN; keep
KEY  (`borrower_id`,`product_id`, status, `created`),  -- good for 1st JOIN; add
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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