MySQL - efficient regexp (or like) query

Question

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?

No, this is never going to be efficient until you change your schema (see Rick's answer below). And 10s of thousands of rows is a small table. — symcbean
– symcbean, Commented Jan 24, 2016 at 1:33

Rick James · Accepted Answer · 2016-01-24 01:49:16Z

1

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                             |
+----------------------------------------------------+

edited Jan 24, 2016 at 1:49

answered Jan 24, 2016 at 0:35

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

stepanian Over a year ago

Thanks for the response. "Add a column to source that has the slug". I'm not sure I understand that. "source" is a column. Did you mean to "redirect"? If that's the case, then it would be a simple join on the slug column right? Why then would I need to use the SUBSTRING_INDEX function?

Rick James Over a year ago

Fixed redirect. SUBSTRING_INDEX was yet another way of dealing with extracting the 'slug'.

JuliaDolgova · Accepted Answer · 2016-01-21 02:59:50Z

0

Maybe

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

But it seems to me it is the same

answered Jan 21, 2016 at 2:59

JuliaDolgova

11 bronze badge

Collectives™ on Stack Overflow

MySQL - efficient regexp (or like) query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related