CODEWITHSUNDEEP
mysqlfull-text-indexing
Gunjan
Gunjan

Reputation: 81

How can I store and index very large text items without knowing the max data size

I am new to MySQL and recently I am working on a Blogging website, but the serious problem which I am facing is how can I store large block of Text data in MySQL. I was thinking of using the type as Text but I think that MySQL doesn't support FULLTEXT search for this type, as a result searching through this might take tremendous amount of time and resource.

Is there an alternative way to store large text in MySQL which can be search very quickly?

Upvotes: 1

Views: 194

Answers (1)

O. Jones
O. Jones

Reputation: 108641

As of version 5.6 MySQL does indeed support FULLTEXT indexing for the TEXT datatype, in InnoDB and MyISAM access-method tables. Check your MySQL server version if you can't get it to work.

You could use VARCHAR(64000) or some such data type to store your text. That will work for almost every blogging application: 64K of the sort of text you can subject to a FULLTEXT search is a lot of text. To compare, the entire text of Shakespeare's HAMLET takes 188K, and so would fit in three of these rows. Therefore, you could adhere to the YAGNI principle and complete your project.

You could easily rig up your table structure to allow continuation rows in the table containing your full text. An extra column that, if not NULL, means "this column continues the text in the row with a certain ID" would work well.

But your best bet is still to get the FULLTEXT index to work on the data type you want.

Edit If you cannot upgrade your old version of the MySQL server at this time, use VARCHAR(64000). Later, when you can upgrade, you can alter the columns to change over to TEXT.

Upvotes: 2

Related Questions