regex - MySQL - efficient regexp (or like) query -

i have 2 tables, performer table , redirect table. performer table has column called slug. redirect table has column called source.

both source , slug columns have unique key indexes.

an example of slug column data like:

this-is-a-slug 

an example of source column data like:

this-is-a-slug.s12345 

i want efficient query gives me rows in redirect have source column starts slug , ".s" characters, followed number digits.

i tried this:

select source redirect join performer on source regexp concat('^', slug, '.s[0-9]+$'); 

it extremely slow. decided less restrictive , tried this:

select source redirect join performer on source concat(slug, ".s%"); 

it still slow.

is there way can efficiently?

abandon current plans.

add column redirect has slug. one-time change table, plus changing code insert it.

if running 5.7 or mariadb, use virtual column, possibly materialized index.

btw, here's way split string:

mysql> select substring_index('this-is-a-slug.s12345', '.', 1); +--------------------------------------------------+ | substring_index('this-is-a-slug.s12345', '.', 1) | +--------------------------------------------------+ | this-is-a-slug                                   | +--------------------------------------------------+ 

if 's' critical, study these:

mysql> select substring_index('this-is-a-slug.s12345', '.s', 1); +---------------------------------------------------+ | substring_index('this-is-a-slug.s12345', '.s', 1) | +---------------------------------------------------+ | this-is-a-slug                                    | +---------------------------------------------------+  mysql> select substring_index('this-is-a-slug.invalid', '.s', 1); +----------------------------------------------------+ | substring_index('this-is-a-slug.invalid', '.s', 1) | +----------------------------------------------------+ | this-is-a-slug.invalid                             | +----------------------------------------------------+