MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data (created datetime,
`created_date` DATE AS (cast(`created` as date)) NOT NULL,  INDEX testin (`created`),                                         -- <-- datetime index w/ cardinality ~ 1M 
  INDEX (`created_date`))
INSERt INTO data (created) VALUEs('2018-02-01'),('2018-02-01'),('2018-02-01'),('2018-02-01')
    SELECT COUNT(*) FROM data
    WHERE YEAR(`created`) = 2018;
    


COUNT(*)


4


    SELECT COUNT(*) FROM data FORCE INDEX (testin)
    WHERE DATE(created) BETWEEN '2018-01-01' AND '2018-12-31';


COUNT(*)


4


    EXPLAIN SELECT COUNT(*) FROM data FORCE INDEX (testin) 
    WHERE DATE(created) BETWEEN '2018-01-01' AND '2018-12-31'


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
data
null
index
null
testin
6
null
4
100.00
Using where; Using index


    SELECT COUNT(*) FROM data 
    WHERE created BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:49:59';


COUNT(*)


4


EXPLAIN     SELECT COUNT(*) FROM data 
    WHERE created BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:49:59';


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
data
null
index
testin
testin
6
null
4
100.00
Using where; Using index