add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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