Member Avatar for Moderns

Hello,

I need to know what's the best way to search tables with million records. I have a table called <people> and this table has the column: <status>

Status might be more than one word like:

"I am happy today and the weather is nice".

Also, it might be in different languages.

Records in table are represented by UTF-8.

I need to search for a sub-word, word, or even some words out of the whole sentence like for example (according the status above):

Search 1: keyword = "ppy"

Search 2: keyword = "am weather"

Search 3: keyword = "nice"

Search 4: keyword = "day weath"

I would greatly appreciate if you hint me to the best method to apply a robust search. As far as I know using "LIKE" is not practical for huge records. I heard about the full text index but never used that.

Thank you very much!

Cheers,

  • 3 Contributors
  • 2 Replies
  • 256 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Zagga
Member Avatar for pritaeas

You can try using LIKE, but enabling full-text search may be a faster alternative.

Member Avatar for Zagga

As far as I understand it, if you have a table that isn't updated very frequently, building an index on the status field would improve search performance with minimal overhead when running SELECT queries. The index has to be rebuilt each time the table is updated though, so if this is very frequent (like a status could be) it will create a lot of extra work for the database, thus decreasing performance. It's a balance bwtewwn the 2.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.