MySQL JSON型のINDEX検証

test.sql

CREATE TABLE `test_json_tbl` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `columns` json DEFAULT (_utf8mb4'[]'),
  `name` text COLLATE utf8mb4_general_ci,
  `selections` json DEFAULT (_utf8mb4'[]'),
  PRIMARY KEY (`id`),
  KEY `idx_columns1` ((cast(json_extract(`columns`,_utf8mb4'$[*].value') as char(512) array))),
  KEY `idx_columns2` ((cast(json_extract(`columns`,_utf8mb4'$[*]._id') as char(64) array))),
  KEY `idx_selections1` ((cast(json_extract(`selections`,_utf8mb4'$[*].value[*]') as char(512) array))),
  KEY `idx_selections2` ((cast(json_extract(`selections`,_utf8mb4'$[*]._id') as char(64) array))),
  FULLTEXT KEY `idx_name1` (`name`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `test_json_tbl` (`id`,`columns`,`name`,`selections`) VALUES (1,'[{\"_id\": \"1\", \"value\": \"ほげ\"}, {\"_id\": \"2\", \"value\": \"ふが\"}, {\"_id\": \"3\", \"value\": \"ぴよ\"}, {\"_id\": \"4\", \"value\": \"わん\"}]',NULL,'[{\"_id\": \"1\", \"value\": [\"さかな\", \"にんげん\", \"ねこ\"]}, {\"_id\": \"2\", \"value\": [\"はちゅうるい\", \"ほにゅうるい\", \"いぬ\"]}]');
INSERT INTO `test_json_tbl` (`id`,`columns`,`name`,`selections`) VALUES (2,'[{\"_id\": \"1\", \"value\": \"hoge\"}, {\"_id\": \"2\", \"value\": \"fuga\"}, {\"_id\": \"3\", \"value\": \"piyo\"}, {\"_id\": \"4\", \"value\": \"wan\"}]',NULL,'[{\"_id\": \"1\", \"value\": [\"魚\", \"人間\", \"猫\"]}, {\"_id\": \"2\", \"value\": [\"爬虫類\", \"哺乳類\", \"犬\"]}]');

-- ref
SELECT
	*
FROM
	test_json_tbl
WHERE
	'piyo' MEMBER OF(columns->'$[*].value')
;

-- ref
SELECT
	*
FROM
	test_json_tbl
WHERE
    '3' MEMBER OF(columns->'$[*]._id')
;

-- range
SELECT
	*
FROM
	test_json_tbl
WHERE
	JSON_CONTAINS(
		columns->'$[*].value',
        JSON_QUOTE('piyo')
    )
    AND
    JSON_CONTAINS(
		columns->'$[*]._id',
        JSON_QUOTE('3')
    )
;

-- fullscan
SELECT
	*
FROM
	test_json_tbl
WHERE
	JSON_SEARCH(columns->'$[*].value', 'all', 'ぴ%')
;

-- ref
SELECT
	*
FROM
	test_json_tbl
WHERE
	'人間' MEMBER OF(selections->'$[*].value[*]')
;

-- ref
SELECT
	*
FROM
	test_json_tbl
WHERE
    '1' MEMBER OF(selections->'$[*]._id')
;

-- range
SELECT
	*
FROM
	test_json_tbl
WHERE
	JSON_CONTAINS(
		selections->'$[*].value[*]',
        JSON_QUOTE('人間')
    )
    AND
    JSON_CONTAINS(
		selections->'$[*]._id',
        JSON_QUOTE('1')
    )
;

-- fullscan
SELECT
	*
FROM
	test_json_tbl
WHERE
	JSON_SEARCH(selections->'$[*].value[*]', 'all', '人%')
;