CODEWITHSUNDEEP
mysqlindexingfull-text-search
Andy Kittisak Intharaphithak
Andy Kittisak Intharaphithak

Reputation: 11

mysql fulltext index

I need MySQL fulltext search to index something like 207/12.25/07/2012 the database has minimum 2 million rows and growing, so correct indexing is absolutely essential. Please help. Right now it seems that slashes and fullstops are omitted and only 2012 is indexed.

Upvotes: 0

Views: 1381

Answers (1)

Joni
Joni

Reputation: 111369

The FULLTEXT index can be used to search for words in larger blocks of text. Seeing that your data does not consist of words, wouldn't a normal index work for you? That would make searches for a prefix like 207/12% fast.

With a normal index searches for a suffix like %25/07/2012 would require a full table scan. If you are searching for suffixes you can create a column that has the data in reverse, and then index and search that column.

If you are searching for something that lies in the middle, like %12.25%, it might make sense to create a FULLTEXT index, you just have to add . into the list of "word characters" (mysql.com) but apparently that requires compiling MySQL from source.

Upvotes: 2

Related Questions