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