1

I have table banners with following column

column type index
id BIGINT primary
start_date DATETIME key
end_date DATETIME NULLABLE key

Executing this query gives me result

EXPLAIN SELECT * FROM banners WHERE end_date IS NULL;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE banners (NULL) ref banners_end_date_index banners_end_date_index 6 const 2 100.00 Using index condition

However, this query gives me result

EXPLAIN SELECT * FROM banners WHERE end_date > '2022-04-26';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE banners (NULL) ALL banners_end_date_index (NULL) (NULL) (NULL) 9,980 50.00 Using where

So does that mean the 2nd query is ignoring the index and doing full table scan?

If the index is being ignored, what query should I use instead to use the index?

Also, I read that OR statement is also ignoring index. Is it always true, or are there exception for index and OR to work together?

Edit:

As pointed out by Bill Karwin in the comment, if most of the data match the condition, it will skip the index. So, I tried again with end_date so far in the future, and here's the EXPLAIN result. Column key and key_len are not NULL, which mean the index is indeed being used.

EXPLAIN SELECT * FROM banners WHERE end_date > DATE('2022-07-20');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE banners (NULL) ALL banners_end_date_index banners_end_date_index 6 (NULL) 74 100.00 Using index condition
Christhofer Natalius
  • 2,727
  • 2
  • 30
  • 39
  • Can you let us know how many records are in the `banners` table roughly? Note that for relatively small tables, MySQL's optimizer might find any index to be "iffy," specifically that an index might be of marginal value. – Tim Biegeleisen Apr 26 '22 at 10:43
  • @TimBiegeleisen It was only 25 rows. I have populated the table to 10k rows and updated the explain table result. The column key, key_len and ref is still NULL. – Christhofer Natalius Apr 26 '22 at 10:49
  • Is `end_date` null for any of those 10K records? – Tim Biegeleisen Apr 26 '22 at 10:50
  • @TimBiegeleisen yes, the end_date column is nullable, and I intentionally set 2 rows to have NULL end_date – Christhofer Natalius Apr 26 '22 at 10:51
  • 1
    If a significantly large portion of the rows matches your condition `end_date > '2022-04-26'`, then the optimizer will choose to skip the index. It assumes that using an index is needless overhead if it's going to have to read most of the rows anyway. I would guess that end date is likely in the future for most of your 10k rows of test data, so it's normal for MySQL to avoid the index in that case. – Bill Karwin Apr 26 '22 at 14:52
  • 1
    As for your second question about `OR` expressions, I wrote a detailed answer about that here: https://stackoverflow.com/questions/13750475/sql-performance-union-vs-or/13866221 – Bill Karwin Apr 26 '22 at 14:57
  • 1
    @BillKarwin Updated the question with 3rd query where end_date only matches <1% of the result, it is indeed has key and key_len and column extra "using index condition". And thank you very much for pointing out your answer in another question. – Christhofer Natalius Apr 27 '22 at 02:51

0 Answers0