CODEWITHSUNDEEP
mysqlregex
stepanian
stepanian

Reputation: 11433

MySQL - efficient regexp (or like) query

I have two tables, a performer table and a redirect table. The performer table has a column called slug. The redirect table has a column called source.

Both the source and slug columns have unique key indexes.

An example of a slug column data is something like: 

this-is-a-slug

An example of a source column data is something like: 

this-is-a-slug.s12345

I want an efficient query that gives me all the rows in redirect that have a source column that starts with a slug and the ".s" characters, followed by a number digits.

I tried this: 

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

It was extremely slow. So I decided to be less restrictive and tried this: 

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

It was still slow.

Is there a way I can do this efficiently?

Upvotes: 0

Views: 107

Answers (2)

Rick James
Rick James

Reputation: 142453

Abandon the current plans.

Add a column to redirect that has the slug. This is a one-time change to the table, plus changing your code to insert it.

If you are running 5.7 or MariaDB, use a virtual column, possibly with a materialized index.

BTW, here's another way to split the string:

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

If the 's' is critical, then 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                             |
+----------------------------------------------------+

Upvotes: 1

JuliaDolgova
JuliaDolgova

Reputation: 1

Maybe 

join performer on left(source,length(slug)+2)=concat(slug, ".s")

But it seems to me it is the same

Upvotes: 0

Related Questions