whats the best way to find sub string in mysql?

[ankit1122]

hello i have a column named products in which differernt shops and businesses insert their product names..if it is like a stationery shop it will be saved as TEXT column as [pen,pencil,sharpner,book,copy,fluid,marker,gel pen,ball pen]...in performance point of view whats the best way to find a sub-string in this column...??? like i want to find if contains "gel pen" or not..??

[minitauros]

So all a shop's products are saved inside a big TEXT field? Isn't the whole idea of SQL that you - in this case - should save all your products in one table, say "products"? That would probably increase performance by itself.

For example:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(64)  | NO   |     | NULL    |       |
| price | decimal(6,4) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

For the rest, usually a VARCHAR is faster to search through than a TEXT field (because of the size). A field containing a lot of text is usually slower to search trhough than a field that contains less text. You can use the LIKE comparison operator to search your text. For as far as I know, that's the best you can get in most situations.

[Taywin]

As minitauros said, you should consider the field type of varchar rather than text. That's the optimization at table level. How to search for a substring, you need to use the like keyword and the '%' at both end of your substring.

SELECT * FROM table_name WHERE field_name like '%sub_string%';