How do I store and index a typical TEXT field in MySQL. In my case the text field will have a max length of 500 Characters, I understand that beyond 255 Chars varchar will behave like a TEXT field, but I need the data to be indexed as well. I will be having where clauses to count certain type of string appearances in the field. Do I store it as TEXT and use a FULLTEXT on that or do I store it as a VARCHAR(500) and Index it?
+1
A:
Native Full Text Indexing on MySQL requires the engine to be MyISAM. Beyond that, I'd recommend using VARCHAR(500) rather than TEXT because:
- TEXT would allow more than 500 characters
- searching against VARCHAR is generally faster than TEXT, indexed or otherwise
OMG Ponies
2010-10-08 05:14:13
when I do a show index on the table I see the Sub_part value for the corresponding column set to 333 when the field is VARCHAR(500), otherwise its NULL. will this cause problems in the queries?
GeekTantra
2010-10-08 05:34:11