3

I'm storing arrays of integers as json in a mariadb (10.3.23) table :

SELECT 
    tag_list 

FROM 
    dw.final_document 

LIMIT 1 ;

Result :

[903, 1258, 1261, 393]

To retrieve entries matching a specific id, this works :

SELECT SQL_NO_CACHE
    count(*) 
     
FROM 
    dw.final_document 

where 
    JSON_CONTAINS(tag_list, 684 ) ;

Result :

+----------+
| count(*) |
+----------+
|     9696 |
+----------+
1 row in set (2.084 sec)

However the performance is not good without an index (2 sec in a 1M rows table)

The possibility to index a specific field of the json is well documented (percona post).

You can add an index on a generated column like this :

ALTER TABLE test_features 
ADD COLUMN street VARCHAR(30) 
GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.properties.STREET'))) VIRTUAL;

Is there a similar way to index arrays of integers ?

GMB
  • 216,147
  • 25
  • 84
  • 135
Bruno
  • 63
  • 6

0 Answers0