By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
char_value VARCHAR(255),
json_value JSON
);
INSERT INTO test (char_value)
WITH RECURSIVE cte (id) AS (
SELECT 1 UNION ALL SELECT id + 1 FROM cte LIMIT 1000
)
SELECT CONCAT('{"key":', RAND() * 100, '}') FROM cte;
UPDATE test SET json_value = char_value;
SELECT char_value, json_value, CAST(json_value AS CHAR) FROM test LIMIT 10;
Records: 1000 Duplicates: 0 Warnings: 0
Rows matched: 1000 Changed: 1000 Warnings: 0
| char_value | json_value | CAST(json_value AS CHAR) |
|---|---|---|
| {"key":36.91424218650371} | 7b226b6579223a2033362e39313432343231383635303337317d | {"key": 36.91424218650371} |
| {"key":6.623920804470703} | 7b226b6579223a20362e3632333932303830343437303730337d | {"key": 6.623920804470703} |
| {"key":22.376880536206887} | 7b226b6579223a2032322e3337363838303533363230363838377d | {"key": 22.376880536206887} |
| {"key":92.01264334098683} | 7b226b6579223a2039322e30313236343333343039383638347d | {"key": 92.01264334098684} |
| {"key":92.93257816967795} | 7b226b6579223a2039322e39333235373831363936373739357d | {"key": 92.93257816967795} |
| {"key":88.62496389879377} | 7b226b6579223a2038382e36323439363338393837393337377d | {"key": 88.62496389879377} |
| {"key":64.32708805829947} | 7b226b6579223a2036342e33323730383830353832393934377d | {"key": 64.32708805829947} |
| {"key":55.76055166848055} | 7b226b6579223a2035352e37363035353136363834383035357d | {"key": 55.76055166848055} |
| {"key":85.82149724086887} | 7b226b6579223a2038352e38323134393732343038363838377d | {"key": 85.82149724086887} |
| {"key":61.825834272267144} | 7b226b6579223a2036312e3832353833343237323236373134347d | {"key": 61.825834272267144} |
-- фиксируем время начала
SET @start_time = CURRENT_TIMESTAMP(6);
-- выполняем поиск по JSON в текстовом поле
SELECT COUNT(*) FROM test WHERE char_value->"$.key" BETWEEN 10 AND 20;
-- смотрим затраченное время, в микросекундах
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, CURRENT_TIMESTAMP(6)) elapsed_time;
| COUNT(*) |
|---|
| 107 |
| elapsed_time |
|---|
| 3617 |
-- фиксируем время начала
SET @start_time = CURRENT_TIMESTAMP(6);
-- выполняем поиск по JSON в поле типа JSON
SELECT COUNT(*) FROM test WHERE json_value->"$.key" BETWEEN 10 AND 20;
-- смотрим затраченное время, в микросекундах
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, CURRENT_TIMESTAMP(6)) elapsed_time;
| COUNT(*) |
|---|
| 107 |
| elapsed_time |
|---|
| 1518 |