MySQL JSONデータにインデックスをつける

投稿日: 投稿者:

ここで教えて頂きました。

show index で確認してみる。

mysql> show index from log_livemap_tonoff\G
*************************** 1. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 9525
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: log_lovemap_tonoff_TM_ADD
 Seq_in_index: 1
  Column_name: TM_ADD
    Collation: A
  Cardinality: 6746
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: depiata
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 481
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`TXT_JSON`,_utf8mb4\'$.depiata\')) as char(3) charset utf8mb4) collate utf8mb4_bin)
*************************** 4. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: arviata
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 449
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`TXT_JSON`,_utf8mb4\'$.arviata\')) as char(3) charset utf8mb4) collate utf8mb4_bin)
*************************** 5. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: updated
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 7996
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`TXT_JSON`,_utf8mb4\'$.updated\')) as char(20) charset utf8mb4) collate utf8mb4_bin)
*************************** 6. row ***************************
        Table: log_livemap_tonoff
   Non_unique: 1
     Key_name: flnum
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 7996
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`TXT_JSON`,_utf8mb4\'$.updated\')) as char(12) charset utf8mb4) collate utf8mb4_bin)
6 rows in set (0.00 sec)

コメントを残す