CODEWITHSUNDEEP
mysql
GeekTantra
GeekTantra

Reputation: 12081

How to store and index a typical TEXT field in MySQL?

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?

Upvotes: 2

Views: 1111

Answers (2)

New Alexandria
New Alexandria

Reputation: 7324

See this awesome question on generating fixed-length hashes for large fields.

Also, Consider building an index table with the "certain type[s] of string appearances", e.g.:

CREATE TABLE main(
  id INTEGER NOT NULL,
  long_text_field TEXT NOT NULL
)

CREATE TABLE main_string(
  main_id INTEGER NOT NULL,
  main_string_types_ud INTEGER NOT NULL
)

CREATE TABLE main_string_types(
  id INTEGER NOT NULL,
  certain_string VARCHAR(255) NOT NULL
)

You can even user a TRIGGER statement to generate insertions for the index & association tables upon INSERT to main, but this will not be efficient. At scale, you should populate that table at fixed time intervals, or async events.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332571

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

Upvotes: 2

Related Questions