MySQL - Most efficient way to share Static Values used in SQL with external applications?

Question

We have a large database system that has a table of static values that are shared with external applications through a simple function call. The purpose is to have exact synchronization matching between the database system and the back-end system, so both systems can reference the same values based on custom names.

The implementation is fairly simple. The table just stores the text name of the system and field (VARCHAR64) along with a value (SMALLINT). This is essentially a lookup table for hardcoded values we share with the back-end JavaScript team, so the data could be:

CUSTOMER_RECORD_STATE, ACTIVE, 1
CUSTOMER_RECORD_STATE, INACTIVE, 2
CUSTOMER_RECORD_STATE, DELETED, 3

The function is used to look these values up as seen below.

SELECT tab.name_first FROM our_customers ourc WHERE ourc.record_state = get_gcs('CUSTOMER_RECORD_STATE','ACTIVE');

Here's the create code for the function:

SET NAMES 'utf8mb4';
DELIMITER $$
CREATE
DEFINER = 'root'@'localhost'
FUNCTION get_gcs (in_system varchar(128), in_field varchar(128), in_value varchar(128))
RETURNS smallint(6)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE var_value smallint DEFAULT -1;

    DECLARE out_result_value smallint;

    DECLARE debug_definition json DEFAULT JSON_OBJECT('function', 'get_gcs');
    DECLARE debug_details json DEFAULT JSON_OBJECT('source', debug_definition, 'parameters',
    JSON_OBJECT('in_system', in_system, 'in_field', in_field, 'in_value', in_value));

    SELECT
        custom_value INTO var_value
    FROM global_custom_setting
    WHERE in_system = name_system
    AND in_field = name_field
    AND in_value = name_value;

    RETURN var_value;
END
$$

DELIMITER ;

character_set_client: utf8mb4

collation_connection: utf8mb4_0900_ai_ci

Database Collation: utf8mb4_unicode_ci

The system is simple and it works well; the back-end team constantly calls this function to retrieve these "static" values defined in the database.

The problem is event though the function is DETERMINISTIC, it appears to dramatically slow down complex SQL calls that have the function embedded in the call. I was surprised by this as I was under the impression that DETERMINISTIC functions would be handled differently; as if they were a static value (in a SQL call).

So questions:

1. Is this the best approach to sharing static values between two platforms (database and backend)?
2. Why is the MySQL engine not treating the calls as DETERMINISTIC and only resolving the value once rather than through what appears to be with each iterative call?

Thanks!

Answer 1

One thing that should (and can) be fixed -- UCASE(in_system) = UCASE(name_system)

If both columns are in the same charset and collation, UCASE is unnecessary. Also, the collation must be ..._ci to be "Case Insensitive".

Any function call hides the columns from use in any index. Hence the WHERE clause in the function cannot use an index.

global_custom_setting needs INDEX(name_system, name_field, name_value). (The columns may be in any order.)

Apparently, the FUNCTION is using collation_connection: utf8mb4_0900_ai_ci, which may be different than the connection's collation.

Suggest trying this after removing the UPPERs and adding the INDEX:

SET NAMES utf8mb4, COLLATE utf8mb4_0900_ai_ci;
DROP FUNCTION get_gcs;
DELIMITER $$
((recreate the function))
DELIMITER ;
((similarly for get_gss))

These seem to apply to strings in a Function:

• Arguments to a function: collation_database
• Literal strings in function: collation_connection
• Table columns: collation of the columns

Functions can never be sargable (that I know of). Some expressions involve "implicit" functions, such as datatype conversion and collation mismatch.