1

I am building an application based on MySQL that allows users to search for some facilities. The facilities are stored in one TEXT column and they are represented as "|"-separated field. Here is the example:

1|2|3|5|6|10|11|12|13|15|17|18|20|21|27|29|30|31|3...

Do you have any idea how I can implement efficient searching for facilities in MySQL? Let's assume that we need to retrieve all rows that "contain" facilities 1, 2 and 15, what would be the query/method? I was trying to use Full-Text indexes but without any luck.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Jacek Francuz
  • 2,420
  • 8
  • 45
  • 61
  • 1
    Yes. Change your database schema. You should **never**, **ever** (I repeat, **never**, **ever**) store data that you need to search or filter on this way in *any* database. – Ken White Dec 30 '11 at 22:40

2 Answers2

2

There is no way to search a symbol-separated string for a given value efficiently.

The best way to improve efficiency in SQL is to use indexes, but you can't use indexes to help find substrings.

The best way to optimize your scenario is to create a child table with a single indexed INT column, and store each of your numeric values on a separate row. Then you can search it efficiently with the aid of the index.

See also my answer to Is storing a comma separated list in a database column really that bad?

Regarding matching multiple facilities when they're stored on separate rows, I've added the sql-match-all tag which is used on Stack Overflow for that type of problem. It comes up frequently, and there are lots of good answers already. Click the tag to find them.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • +1 (also gave your linked question a +1). Isn't it amazing that there are 4 million questions about comma or delim separated values in databases, with answers that all say "Don't do that.", and then someone posts another question about the same thing? – Ken White Dec 30 '11 at 22:39
  • 1
    @KenWhite: It could be that people naturally don't want to sift through 4 million questions, and I don't blame them for that! :-) – Bill Karwin Dec 30 '11 at 22:41
  • 1
    Also, the answer is "don't do that in a *relational* database." But there could be other non-relational databases that do support set or array data types, and indexes for the individual elements. – Bill Karwin Dec 30 '11 at 22:43
  • Second point taken (re: relational DBs). Also, I meant I had upvoted your linked *answer*, of course. :) Regarding your first comment, they don't have to sift through 4M; reading a few should suffice to make the point. If I read 10 questions about something, and every single answer says "Don't", I get the point pretty quickly that I should think about doing it differently if the need arises. – Ken White Dec 30 '11 at 22:49
0

You could use the FIND_IN_SET() function. However, this only works on comma-separated lists, so you'd have to pre-process your fields:

SELECT ... WHERE FIND_IN_SET('something', STR_REPLACE(your_text_field, '|', ','));

Performance will probably be pretty crappy, since you'll be doing your searching on derived fields, which precludes using indexes. You'd be better off normalizing the design, and splitting those values into a child table.

Marc B
  • 356,200
  • 43
  • 426
  • 500