Indexing column with REPLACE function in mySQL

Question

May be a silly question to ask.

I need to use this a lot in both join and where:

REPLACE(table_a.column_1, '-', '') = REPLACE(table_b.column_2, '-', '')

since bit data inconsistency from other companies' system

Is that possible or any other database type can have index of a column with REPLACE function? eg:

ALTER TABLE `table_a` ADD INDEX ( REPLACE(`column_1`, '-', '') ) ;

Database Type: MyISAM

Why is it that `table_a.column_1 = table_b.column_2` would not work here? — Achrome, Aug 05 '13 at 08:10
I read it somewhere that using a Funtion on variable doesn't use index nor you can create index on function, as functions output is not determined, So, my answer is no — Sumit Gupta, Aug 05 '13 at 08:10
@AshwinMukhija maybe his data has more - from other company db then they have... — Sumit Gupta, Aug 05 '13 at 08:11
@Sumit Gupta yup exactly, tables are from different companies, the trigger suggested by Sylvain will do the ticks, thx — Richard Fu, Aug 15 '13 at 02:16

score 2 · Accepted Answer · edited May 23 '17 at 12:20

There is no such thing as computed column in MySQL.

If you want to format some value to speed-up index search, you will probably have to use some trigger. As a matter of fact, I answered almost the same question this morning: see https://stackoverflow.com/a/18047511/2363712 for a similar example.

In your case, that would lead to something like that:

CREATE TABLE `table_a`(`column_1` VARCHAR(255), ... ,
                       `column_1_fmt` VARCHAR(255),
                       INDEX(`column_1_fmt`));

CREATE TRIGGER ins_table_a BEFORE INSERT ON `table_a`
FOR EACH ROW
    SET NEW.`column_1_fmt` = REPLACE(NEW.column_1, '-', '');

CREATE TRIGGER upd_table_a BEFORE UPDATE ON `table_a`
FOR EACH ROW
    SET NEW.`column_1_fmt` = REPLACE(NEW.column_1, '-', '');

Now you will use column_1_fmt to search for values/join on values having the required format.

Concerning your special need (removing dashes -- from some kind of serial/reference number?). Maybe you should reverse the problem. Store those value as canonical form (no dash). And add required dashes at SELECT time.

Indexing column with REPLACE function in mySQL

1 Answers1

Linked