MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.13


 CREATE TABLE t1 (a TINYINT NOT NULL, KEY(a));
 CREATE TABLE t2 (a TINYINT, KEY(a));
SHOW CREATE TABLE t1


Table
Create Table


t1
CREATE TABLE `t1` (
  `a` tinyint(4) NOT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


SHOW CREATE TABLE t2


Table
Create Table


t2
CREATE TABLE `t2` (
  `a` tinyint(4) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
EXPLAIN SELECT * FROM t1 WHERE a!=a;


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


1
SIMPLE
null
null
null
null
null
null
null
null
null
Impossible WHERE


EXPLAIN SELECT * FROM t2 WHERE a!=a;


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


1
SIMPLE
t2
null
index
null
a
2
null
5
80.00
Using where; Using index


EXPLAIN SELECT * FROM t1 WHERE a<a;


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


1
SIMPLE
null
null
null
null
null
null
null
null
null
Impossible WHERE


EXPLAIN SELECT * FROM t2 WHERE a<a;


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


1
SIMPLE
t2
null
index
null
a
2
null
5
33.33
Using where; Using index


EXPLAIN SELECT * FROM t1 WHERE a>a;


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


1
SIMPLE
null
null
null
null
null
null
null
null
null
Impossible WHERE


EXPLAIN SELECT * FROM t2 WHERE a>a;


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


1
SIMPLE
t2
null
index
null
a
2
null
5
33.33
Using where; Using index


EXPLAIN SELECT COUNT(*) FROM t1 WHERE not (a!=a);


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


1
SIMPLE
t1
null
index
null
a
1
null
5
100.00
Using index


EXPLAIN SELECT COUNT(*) FROM t2 WHERE not (a!=a);


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


1
SIMPLE
t2
null
index
null
a
2
null
5
20.00
Using where; Using index