7.15.14. in_values

7.15.14.1. Summary

New in version 4.0.7.

in_values enables you to simplify the query which uses multiple OR or ==. It is recommended to use this function in point of view about performance improvements in such a case.

7.15.14.2. Syntax

in_values requires two or more arguments - target_value, multiple value, and options.

in_values(target_value, value1, ..., valueN)
in_values(target_value, value1, ..., valueN, {"option": "value of option"}

7.15.14.3. Usage

Here is a schema definition and sample data.

Sample schema:

Execution example:

table_create Tags TABLE_PAT_KEY ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
table_create Memos TABLE_HASH_KEY ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Memos tag COLUMN_SCALAR ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Tags memos_tag COLUMN_INDEX Memos tag
# [[0,1337566253.89858,0.000355720520019531],true]

Sample data:

Execution example:

load --table Memos
[
{"_key": "Groonga is fast",          "tag": "groonga"},
{"_key": "Mroonga is fast",          "tag": "mroonga"},
{"_key": "Rroonga is fast",          "tag": "rroonga"},
{"_key": "Droonga is fast",          "tag": "droonga"},
{"_key": "Groonga is a HTTP server", "tag": "groonga"}
]
# [[0,1337566253.89858,0.000355720520019531],5]

Here is the simple usage of in_values function which selects the records - the value of tag column is “groonga” or “mroonga” or “droonga”.

Execution example:

select Memos --output_columns _key,tag --filter 'in_values(tag, "groonga", "mroonga", "droonga")' --sort_keys _id
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         4
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "tag",
#           "ShortText"
#         ]
#       ],
#       [
#         "Groonga is fast",
#         "groonga"
#       ],
#       [
#         "Mroonga is fast",
#         "mroonga"
#       ],
#       [
#         "Droonga is fast",
#         "droonga"
#       ],
#       [
#         "Groonga is a HTTP server",
#         "groonga"
#       ]
#     ]
#   ]
# ]

When executing the above query, you can get the records except “rroonga” because “rroonga” is not specified as value in in_values.

Then, you can also specify options of in_values. Currently, you can only specify too_many_index_match_ratio. The type of this value is double.

You can change the value of GRN_IN_VALUES_TOO_MANY_INDEX_MATCH_RATIO with too_many_index_match_ratio. The default value of GRN_IN_VALUES_TOO_MANY_INDEX_MATCH_RATIO is 0.01. GRN_IN_VALUES_TOO_MANY_INDEX_MATCH_RATIO is used for deciding whether in_values use an index or not.

There is a case that sequential search is faster than index search when the number of narrowed down records is small enough in contrast to the number of expected records to narrow down by in_values with AND operation which use indexes.

For example, suppose you narrow down records by --filter and you narrow down them by in_values.

In the default, in_values use sequential search in the following case.

1. If you narrow down records to 1,000 by --filter and records of the target of in_values are 500,000.

   1,000/500,000 = 0.002 < 0.01(GRN_IN_VALUES_TOO_MANY_INDEX_MATCH_RATIO) -> in_values use sequential search.
   

On the other hand, in_values use index in the following case.

2. If you narrow down records to 1,000 by --filter and records of the target of in_values are 50,000.

   1,000/50,000 = 0.02 > 0.01(GRN_IN_VALUES_TOO_MANY_INDEX_MATCH_RATIO) -> in_values use index.
   

Here is a query to set options of in_values:

Execution example:

select Memos --output_columns _key,tag --filter 'in_values(tag, "groonga", "mroonga", "droonga", {"too_many_index_match_ratio":0.001})' --sort_keys _id
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         4
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "tag",
#           "ShortText"
#         ]
#       ],
#       [
#         "Groonga is fast",
#         "groonga"
#       ],
#       [
#         "Mroonga is fast",
#         "mroonga"
#       ],
#       [
#         "Droonga is fast",
#         "droonga"
#       ],
#       [
#         "Groonga is a HTTP server",
#         "groonga"
#       ]
#     ]
#   ]
# ]

7.15.14.4. Parameters

There are two or more required parameter, target_value and multiple value.

7.15.14.4.1. target_value

Specifies a column of the table that is specified by table parameter in select.

7.15.14.4.2. value

Specifies a value of the column which you want to select.

7.15.14.4.3. {"option": "value of option"}

Specify in_values’s option. Currently, you can only specify too_many_index_match_ratio. The type of this value is double.

7.15.14.5. Return value

in_values returns whether the value of column exists in specified the value of parameters or not.

If record is matched to specified the value of parameters, it returns true. Otherwise, it returns false.