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
  • 293 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Member #671080

Dani AI

Generated

Short answer: use a FULLTEXT index for ordinary word/phrase searches, and use an ngram/full‑text parser or a dedicated search engine for true substring matching.

: for word-based queries like "am weather" or "nice" the built‑in MySQL FULLTEXT index + MATCH() … AGAINST() (natural or boolean mode) is far faster and more appropriate than scanning millions of rows with wildcard pattern scans. Fulltext is supported for InnoDB and MyISAM and returns relevance scores you can sort by. (dev.mysql.com)

To apply that to your samples:

  • To require multiple words anywhere in the status use boolean mode: MATCH(status) AGAINST('+word1 +word2' IN BOOLEAN MODE). Note that boolean mode supports the * truncation operator for word prefixes (e.g. nice*) but not leading wildcards. Also watch for stopwords and the minimum token length—short tokens like am are often not indexed by default. You will likely need to tune innodb_ft_min_token_size / ft_min_word_len and rebuild indexes. (dev.mysql.com)

If you must match substrings inside words (your ppy example or day weath partial fragments), the default word tokenizer won’t help. Two practical options:

  1. Use MySQL’s ngram fulltext parser (create the FULLTEXT index WITH PARSER ngram and set ngram_token_size at server startup). Choose the n‑gram length (2 or 3, etc.) to capture the substring sizes you care about, then rebuild the index. Example:

    ALTER TABLE people ADD FULLTEXT INDEX ft_status (status) WITH PARSER ngram;
# set in my.cnf: [mysqld]
# ngram_token_size=3

    This makes internal substrings searchable, but increases index size and changes match behavior. (dev.mysql.com)

  2. For the most robust multilingual / substring / fuzzy needs, use a search engine (Elasticsearch, Sphinx). They provide tuned ngram/edge_ngram analyzers, language-specific tokenizers, and better control over index size vs. match behavior. Tradeoffs: extra service and sync work, but much more flexible and performant at scale. (elastic.co)

Operational notes you won’t see in the thread: fulltext index maintenance has write overhead (InnoDB processes FTS updates at commit time), so if statuses are updated very frequently consider asynchronous indexing or a separate search index to avoid write latency. Test with your actual data—stopwords, token size, and charset/collation settings all change results and performance. (dev.mysql.com)

Recommended Answers

All 2 Replies

Member Avatar for pritaeas

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

Member Avatar for Member #671080

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.