CODEWITHSUNDEEP
phpmysqlsqlregex
Johnny
Johnny

Reputation: 7

REGEP Slowdown query in Mysql

How to query comma delimited field of table to see if number is within the field

I want to select rows from a table if a column contains a certain number e.g 981. The data in these columns is either 0, null or in the format below:

1007,1035,1189,908,977,974,979,973,982,981,1007

I made a like as in above link. I used IN ('7','3','12','1','10','13','2') & it gets only 22K of rows from Table. For the same query If i use REGEXP '7|3|12|1|10|13|2' it returs nearly 119K rows from same Table. but REGEXP makes slow the query. is there any method to make my query faster?

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Your regexp is confusing values. So, 7 matches 17 and 77. The in does what you want.

First, I don't recommend storing values in a comma-delimited list. If you really have to (like someone who didn't know better designed the database), then use find_in_set():

where find_in_set(7, list) > 0 or
      find_in_set(3, list) > 0 or
      . . .

Alternatively, put delimiters in the regexp. I think it would look like:

where concat(',', list, ',') regexp '(,7,)|(,3,)|(,12,)|(,1,)|(,10,)|(,13,)|(,2,)'

Upvotes: 1

Related Questions