CODEWITHSUNDEEP
mysqlregexperformance
Akxe
Akxe

Reputation: 11575

Regex vs Join Performance Mysql

I am at hesitation what type should I use. There will be really a lot of IDs and Groups, normally they learned me to do multiple tables but the second version with regexp seems great and less redundant. I would love to know with of those two version is better.

+----+   +----+-------+
| ID |   | ID | Group |
+----+   +----+-------+
| 1  |   | 1  |  gr1  |
| 1  |   | 1  |  gr2  |
| 2  |   | 2  |  gr2  |
| 2  |   | 2  |  gr3  |
+----+   +----+-------+
SELECT * FROM tbl1 join tbl2 USING(ID) WHERE Group="gr1";

+----+-----------+
| ID | Group     |
+----+-----------+
| 1  |  gr1,gr2  |
| 2  |  gr2,gr3  |
+----+-----------+
SELECT * FROM tbl1 WHERE Group REGEXP '(^|,)gr1($|,)';

Upvotes: 3

Views: 276

Answers (1)

Lorenz Meyer
Lorenz Meyer

Reputation: 19945

As long as you have small tables, you can use both at your discretion.

If you expect the table to grow, you really need to opt for the first choice. The reason behind is that a query with where regexp will not make use of the indexes. And as you know, indexes are the key to fast queries.
If you're using InnoDB, define a foreign key that links the two tables.

You say, you find the first choice having more redundance than the second one. It doesn't seem to me. Thinking about this your sketch might show that there is a misunderstanding. It should rather look like this, i.e. there is only one row per id in table 1, and therefore, there is no redundance.

      Tbl1                Tbl2
+----+----------+   +----+-------+
| ID |Other cols|   | ID | Group |
+----+----------+   +----+-------+
| 1  |          |   | 1  |  gr1  |
| 2  |          |   | 1  |  gr2  |
+----+----------+   | 2  |  gr2  |
                    | 2  |  gr3  |
                    +----+-------+

Upvotes: 5

Related Questions