I'm wondering how/if I can improve the regex I'm using in a query. I have a set of identifiers for certain user groups. They can be in two main format:

  • X123 or XY12, and
  • Any two letter combo

A user can belong to multiple groups, in which case different groups are separated by a pound symbol (#). Here's an example:

groups     user     age
X124       john     23
XY22#AB    mike     33
AB         peter    21
X122#XY01  francis  43

I want to count rows in which at least one group in second format appears, i.e. where a user is not exclusively a member of groups in first the format. I'm currently doing it like this:

  groups not rlike '^(X[Y1-9][0-9]{2,2})(#X[Y1-9][0-9]{2,2})*$'

Is this bad performance-wise? And how should I approach fixing it?


1 Answer 1


Looks good to me.

Your specification, "at least one group in second format appears", is stated in the positive, yet your not rlike is stated in the negative. You might consider phrasing it as where groups rlike '(^|#)[A-Z][A-Z](#|$)'.

You asked about performance. These queries will certainly have to read all rows, which costs more CPU cycles than evaluating the regexes. You might consider adding derived boolean column(s) that indicate whether "format1" and "format2" groups are present. Then you could index on such a column.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.