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

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.

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%';
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.