Oracle vs MySQL – Functional Indexes Comparison

case sensitiveindexmysql-5.7

Hoping you can help me understand a particular problem I'm facing when converting an Oracle 12c database to MySQL 5.7.

For context, let's say we have a table that looks a bit like this:

id    firstname    lastname
---------------------------
1     John         Jones
2     tom          smith
3     ALEX         JonEs

Data can be entered into the table in any case or mixed case, there is no control at the application level or any constraints or triggers at the database level.

So for the purpose of effective querying, a functional index has been created in Oracle as follows, because we will always query the lastname field using the lower case function:

CREATE INDEX idx_table1_lastname ON schema1.table1 (LOWERCASE(lastname))

We can't do that in MySQL because functional indexes are not a thing. The same column in MySQL has been created as collation utf8-general-ci so my question is this.

If we create the same index on our MySQL database, we can only do:

CREATE INDEX idx_table1_lastname ON table1 (lastname)

Will the index be effective, given the collation of our column is case-insensitive? Or should we be putting something in place to ensure all our database writes for this field are being written in a consistent case?

Thanks

Best Answer

MySQL does not have functional indexes but it does have (at least in version 5.7 and in MariaDB 5.5+) generated columns which can be indexed.

For details see the official documentation: Generated Columns.

Example of usage:

ALTER TABLE schema1.table1
    ADD lower_lastname VARCHAR(100) AS (LOWER(lastname)) ;

CREATE INDEX idx_table1_lastname
    ON schema1.table1 (lower_lastname) ;

And test at dbfiddle.uk where you can see that the index is used.

Now, the above construction is not needed if you never need to distinguish 'Jack' from 'jack' and you have defined the column with a case insensitive collation. You can just the case insensitive column and index it.

The construction is useful if you want to have case sensitive and case insensitive comparisons and also in other areas where the Oracle database has functional indexes (i.e. with numeric or date columns).

Related Question