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

2 Years
Discussion Span
Last Post by Taywin

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%';

Edited by Taywin

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.