MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test1 (
id INT,
data JSON
);

CREATE TABLE test2 (
id INT,
data TEXT CHECK (JSON_VALID(data)), 
INDEX idx_data (data(100))
);
SET SESSION cte_max_recursion_depth = 100000;

INSERT INTO test1 
WITH RECURSIVE cte AS (
  SELECT 1 id UNION ALL SELECT id + 1 FROM cte LIMIT 100000
)
SELECT id MOD 20, JSON_OBJECT('id', id, 'data', ROUND(RAND() * 20))
FROM cte;

INSERT INTO test2 
SELECT *
FROM test1;

Records: 100000  Duplicates: 0  Warnings: 0



Records: 100000  Duplicates: 0  Warnings: 0


SET @start = NOW(6);

SELECT * FROM test1 WHERE data = '{"id": 5, "data": 14.0}' LIMIT 1;

SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6));

EXPLAIN 
SELECT * FROM test1 WHERE data = '{"id": 5, "data": 14.0}';


id
data




TIMESTAMPDIFF(MICROSECOND, @start, NOW(6))


82497




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


1
SIMPLE
test1
null
ALL
null
null
null
null
100000
10.00
Using where


SET @start = NOW(6);

SELECT * FROM test2 WHERE data = '{"id": 5, "data": 14.0}' LIMIT 1;

SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6));

EXPLAIN 
SELECT * FROM test2 WHERE data = '{"id": 5, "data": 14.0}';


id
data




TIMESTAMPDIFF(MICROSECOND, @start, NOW(6))


369




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


1
SIMPLE
test2
null
ref
idx_data
idx_data
403
const
1
100.00
Using where