This topic lists the system functions that can be directly or conditionally used for function-based indexes.
System functions without requirements on the data type
The following table lists the functions whose expressions can be used for function-based indexes.
| TRIM | ORD | IS_IPV6 | POINT |
| SUBSTR | LTRIM | IS_IPV4_MAPPED | LINESTRING |
| SUBSTRING_INDEX | RTRIM | IS_IPV4_COMPAT | MULTIPOINT |
| MD5 | LPAD | CRC32 | MULTILINESTRING |
| IP2INT | BIT_COUNT | TO_BASE64 | POLYGON |
| INT2IP | FIND_IN_SET | FROM_BASE64 | MULTIPOLYGON |
| INSERT | LEFT | SHA | GEOMCOLLECTION |
| UNHEX | MAKE_SET | SHA2 | ST_ASTEXT |
| TIMEDIFF | SECOND | UNCOMPRESS | ST_BUFFER_STRATEGY |
| PERIOD_DIFF | MINUTE | UNCOMPRESSED_LENGTH | ST_BUFFER |
| STR_TO_DATE | MICROSECOND | UUID_TO_BIN | ST_ASWKT |
| FROM_DAYS | TO_SECONDS | IS_UUID | ST_DISTANCE |
| FLOOR | TIME_TO_SEC | UUID_TO_BIN | ST_ISVALID |
| CEIL | SEC_TO_TIME | FORMAT_BYTES | ST_ASWKB |
| REPEAT | INTERVAL | FORMAT_PICO_TIME | ST_ASBINARY |
| REPLACE | SQRT | DECODE | ST_DISTANCE_SPHERE |
| LOCATE | LOG2 | ENCODE | ST_WITHIN |
| REVERSE | LOG10 | LN | ST_CONTAINS |
| RIGHT | TRUNCATE | LOG | ST_AREA |
| RPAD | EXP | ASIN | ST_INTERSECTS |
| CHAR_LENGTH | HOUR | ACOS | ST_X |
| IFNULL | MID | ATAN | ST_Y |
| CONCAT_WS | RADIANS | ATAN2 | ST_LATITUDE |
| FIELD | MAKETIME | COS | POWER |
| NULLIF | FORMAT | TAN | LENGTHC |
| CHAR_LENGTH | COT | SIN | COMPRESS |
| POSITION | BIT_LENGTH | MAKEDATE | INSTRC |
| ELT | PI | PERIOD_ADD | CHAR |
| STRCMP | DEGREES | TIME_FORMAT | SOUNDEX |
| CEILING | EXPORT_SET | ST_LONGITUDE | LENGTH |
| TIME | IS_IPV4 | ST_TRANSFORM | CONVERT |
| COALESCE | ASCII | CONVERT_TZ | HEX |
| NVL | INSTR | SPACE | INET6_NTOA |
| INET6_ATON | INET_ATON | GREATEST | LEAST |
| UPPER | LOWER | SUBTIME | ADDTIME |
| DATE_FORMAT | ROUND | GET_FORMAT | CHARSET |
System functions with requirements on the data type
The following system functions are prohibited in function-based indexes when the data type is TIME. For example, the DATE function is prohibited in a function-based index when the value of the first parameter is of the TIME data type. The DATEDIFF function is prohibited in a function-based index when the value of any parameter is of the TIME data type.
| DATE | TIMESTAMP | MONTHNAME | ADDDATE |
| DATE_SUB | DAY | MONTH | TIMESTAMP_NVL |
| DAYNAME | YEAR | TIMESTAMPDIFF | QUARTER |
| DAYOFMONTH | WEEKOFYEAR | TO_DAYS | DAYOFWEEK |
| WEEKDAY | DATEDIFF | DAYOFYEAR | YEARWEEK |
| TIMESTAMPADD | LAST_DAY | WEEK | - |
JSON functions
Expressions of the following JSON functions can be used in function-based indexes.
| JSON_EXTRACT | JSON_TYPE | JSON_CONTAINS | JSON_LENGTH |
| JSON_CONTAINS_PATH | JSON_INSERT | JSON_DEPTH | JSON_STORAGE_SIZE |
| JSON_KEYS | JSON_STORAGE_FREE | JSON_ARRAY | JSON_MERGE_PRESERVE |
| JSON_QUOTE | JSON_MERGE | JSON_UNQUOTE | JSON_MERGE_PATCH |
| JSON_OVERLAPS | JSON_PRETTY | JSON_REMOVE | JSON_SET |
| JSON_SEARCH | 1 MEMBER OF | JSON_VALID | JSON_VALUE |
| JSON_ARRAY_APPEND | JSON_OBJECT | JSON_ARRAY_INSERT | JSON_QUERY |
| JSON_REPLACE | - | - | - |
At present, the following function can be used only in predicates.
| JSON_EQUAL | JSON_EXISTS |
CAST expressions
When the target or source type of a CAST expression is a time type, the CAST expression cannot be used in function-based indexes. The following table lists the detailed scenarios.
Source type |
Target type |
Description |
|---|---|---|
| TIME | DATETIME/TIMESTAMP/DATE | Prohibited in function-based indexes |
| datetime/TIMESTAMP/DATE | TIMESTAMP WITH TIME ZONE/TIMESTAMP WITH LOCAL TIME ZONE | Prohibited in function-based indexes |