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 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