How to create an index on a CONCAT("string" ,column) in mysql?

Question

I have a table where id is primary key.

CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT,
  col1 VARCHAR(45) NULL,
  PRIMARY KEY (id));

I have another table t2 which is joining table t1 as

t2 LEFT JOIN t1 ON CONCAT("USER_", t1.id) = t2.user_id

I want to create an index which has CONCAT("USER_", t1.id) values indexed in any order.

I tried

ALTER TABLE t1 ADD INDEX ((CONCAT('user_',id) DESC);

but it is giving error. I have followed official documentation of mysql.

Note : I do not want to create a new CONCAT("user_", id) column.

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-column-prefixes

Work in the other direction, change t2.user_id so that it does NOT require a constant prefix of USER_ which adds no benefits but does deliver disadvantages. t2.user_id should be an integer, not a string — Paul Maxwell
– Paul Maxwell, Commented Jan 12, 2019 at 5:06
I understand that but this is a legacy system and I cannot change tables. Is there is no way to create index like that ? — Manish Verma
– Manish Verma, Commented Jan 12, 2019 at 5:14
To solve this issue you will be changing some tables, you need generated columns, then indexes. — Paul Maxwell
– Paul Maxwell, Commented Jan 12, 2019 at 6:53
@Used_By_Already Generated column cannot refer to auto-increment column. And id is Auto-increment column. — Manish Verma
– Manish Verma, Commented Jan 12, 2019 at 7:57

Paul Maxwell · Accepted Answer · 2019-01-13 23:40:48Z

InnoDB supports secondary indexes on virtual generated columns. https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

In 5.7(onward) you can use a generated column, then index that column. e.g.

Here is an example of taking the integer out of the string to create an efficient join:

CREATE TABLE myusers (
    id mediumint(8) unsigned NOT NULL auto_increment
  , name varchar(255) default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1
;

INSERT INTO myusers (`name`) VALUES ('Imelda'),('Hamish'),('Brandon'),('Amity'),('Jillian'),('Lionel'),('Faith'),('Dai'),('Reed'),('Molly');

CREATE TABLE mytable (
    id mediumint(8) unsigned NOT NULL auto_increment
  , user_id VARCHAR(20)
  , ex_user_id integer GENERATED ALWAYS AS (0+substring(user_id,6,20))
  , password varchar(255)
  , PRIMARY KEY (`id`)
  , INDEX idx_ex_user_id (ex_user_id)
) AUTO_INCREMENT=1
;

INSERT INTO mytable (`user_id`,`password`) VALUES 
 ('user_1','PYX68BIC9RD')
,('user_2','LPY07EIN0UA')
,('user_3','UGC24TKI3JL')
,('user_4','YQU18ALB8YA')
,('user_5','DEL56AGR6AD')
,('user_6','YQN87UOB0PO')
,('user_7','CPC15JFU6MC')
,('user_8','MWC40ZWD2EE')
,('user_9','HEB34QQH0UM')
,('user_10','GVP36PLP5PW')
;

select
*
from myusers
inner join mytable on myusers.id = mytable.ex_user_id
;

id | name    | id | user_id | ex_user_id | password   
-: | :------ | -: | :------ | ---------: | :----------
 1 | Imelda  |  1 | user_1  |          1 | PYX68BIC9RD
 2 | Hamish  |  2 | user_2  |          2 | LPY07EIN0UA
 3 | Brandon |  3 | user_3  |          3 | UGC24TKI3JL
 4 | Amity   |  4 | user_4  |          4 | YQU18ALB8YA
 5 | Jillian |  5 | user_5  |          5 | DEL56AGR6AD
 6 | Lionel  |  6 | user_6  |          6 | YQN87UOB0PO
 7 | Faith   |  7 | user_7  |          7 | CPC15JFU6MC
 8 | Dai     |  8 | user_8  |          8 | MWC40ZWD2EE
 9 | Reed    |  9 | user_9  |          9 | HEB34QQH0UM
10 | Molly   | 10 | user_10 |         10 | GVP36PLP5PW

explain select
*
from myusers
inner join mytable on myusers.id = mytable.ex_user_id
;

id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref                                    | rows | filtered | Extra      
-: | :---------- | :------ | :--------- | :--- | :------------- | :------------- | :------ | :------------------------------------- | ---: | -------: | :----------
 1 | SIMPLE      | myusers | null       | ALL  | PRIMARY        | null           | null    | null                                   |   10 |   100.00 | null       
 1 | SIMPLE      | mytable | null       | ref  | idx_ex_user_id | idx_ex_user_id | 5       | fiddle_HNTHMETRTFAHHKBIGWZM.myusers.id |    1 |   100.00 | Using where

db<>fiddle here

note the conversion of user_id from string to integer is "implicit":

To cast a string to a number, you normally need do nothing other than use the string value in numeric context: https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

Generated column cannot refer to auto-increment column. And id is Auto-increment column.
@ManishVerma I have re-worked my example, perhaps it will now it will work for you

Collectives™ on Stack Overflow

How to create an index on a CONCAT("string" ,column) in mysql?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related