dcm4chee-arch-light 5.12.0 - mysql create-case-insentive-index

[Docjay]

v dcm4chee-arc-light 5.12.0
database:  mysql 5.7

a while back while I was installing dcm4chee 5.11.0 I could never create the index for the 'case insensitive search' from the file 'create-case-insensitive-index.sql''. 

MYSQL always complained about the SQL syntax.

Here is the original file:

create index family_name_upper_idx on person_name (upper(family_name));
create index given_name_upper_idx on person_name (upper(given_name));
create index middle_name_upper_idx on person_name (upper(middle_name));

create index series_desc_upper_idx on series (upper(series_desc));
create index study_desc_upper_idx on study (upper(study_desc));

I've read here at the bottom of the page, that adding MYSQL indexes like this isn't possible, but one can index a generated column:

Since MySQL 5.7 you can index a generated columns as follows:

ALTER TABLE employees
  ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name));

CREATE INDEX emp_up_name ON employees (last_name_up);

Here is my proposed revision of the above .sql file.

I haven't tried this, but just getting others thoughts first.

ALTER TABLE person_name ADD COLUMN family_name_up VARCHAR(255) AS (UPPER(family_name));
create index family_name_upper_idx on person_name (UPPER(family_name_up));

ALTER TABLE person_name ADD COLUMN given_name_up VARCHAR(255) AS (UPPER(given_name));
create index given_name_upper_idx on person_name (upper(given_name_up));

ALTER TABLE person_name ADD COLUMN middle_name_up VARCHAR(255) AS (UPPER(middle_name));
create index middle_name_upper_idx on person_name (upper(middle_name_up));

ALTER TABLE series ADD COLUMN series_desc_up VARCHAR(255) AS (UPPER(series_desc));
create index series_desc_upper_idx on series (upper(series_desc_up));

ALTER TABLE study ADD COLUMN study_desc_up VARCHAR(255) AS (UPPER(study_desc));
create index study_desc_upper_idx on study (upper(study_desc_up));

[Docjay]

I still can't get the above index created, even with 5.13.2.  Can someone shed some light on whether this index is needed,or why mysql doesn't like how it is currently written?

Thank you

[gunterze]

You don't need that indexes for MySQL, because MySQL queries are not case-sensitive by default. Actually the current version for MySQL does not support case sensitive matching for any supported matching key!

[Docjay]

Good to know, thanks for the update. I didn't want to be without any index that was included with the install.