amitsolanki24_'s avatar

Search like search engine.

How to Improve Search Results for "GCP" in MySQL Full-Text Search?

Hey everyone,

I'm facing an issue with search functionality in my project. When a user types "GCP" in the search field, I expect the following results:

  1. What is GCP?
  2. Advantages of GCP.
  3. Why we use Google Cloud Platform.
  4. Types of Google Cloud Platforms.

However, my search query only returns the first two results:

  1. What is GCP?
  2. Advantages of GCP.

Queries I Have Tried:

  1. Full-Text Search (Boolean Mode) 
    SELECT * FROM posts WHERE MATCH(title) AGAINST('gcp' IN BOOLEAN MODE);
  2. Full-Text Search (Natural Language Mode) 
    SELECT * FROM posts WHERE MATCH(title) AGAINST('gcp' IN NATURAL LANGUAGE MODE);
  3. Laravel Query Builder 
    Post::whereRaw("MATCH(title) AGAINST('gcp' IN BOOLEAN MODE)")->get();
  4. Algolia Search – Also did not return all four records.

Despite trying different approaches, some relevant results are missing. How can I ensure all four expected results appear when searching for "GCP"?

Any suggestions or improvements are highly appreciated!

Thanks in advance!

0 likes
11 replies
JussiMannisto's avatar

The database doesn't know what GCP is or that it relates to Google Cloud Platform, so you have to add that information in your posts somehow. I'd use tags.

You could add a tags column containing a comma-separated list of tags, e.g. gcp, google cloud platform. But that would produce false positives when using full text matching, e.g. the term random number would match the tags random access memory, phone number, which you might not want.

A better approach would be to add a post_tags relation with a tag column:

// Post.php
public function tags(): HasMany {
	return $this->hasMany(PostTag::class);
}

Then you could do this:

$posts = Post::query()
	->whereFullText('title', $term)
	->orWhereHas('tags', fn($query) => $query->where('tag', $term))
	->get();

// Or with full-text matching:
...
	->orWhereHas('tags', fn($query) => $query->whereFullText('tag', $term))
...

Just remember to add an index on the tag column.

1 like
amitsolanki24_'s avatar

@JussiMannisto okay so I have to add tags for that.

And what do you think about aws open-search or elastic-search does it help me?

Snapey's avatar

@amitsolanki24_ maybe only AI might know that GCP and Google Cloud Platform are the same thing. Your goal is unreasonable.

JussiMannisto's avatar

@amitsolanki24_ It can make your queries a lot faster and more scalable. You can do fuzzy searches and define synonyms, such as gcp => google cloud platform. But you still have to do the work yourself.

amitsolanki24_'s avatar

@Snapey yes it could be GCP could be Good Clinical Practice so how can I achieve this on realtime when user search anything.

JussiMannisto's avatar

@amitsolanki24_ If you don't feel like defining synonyms or tags, you can write your own search engine that follows any logic that you want. Normal search engines wouldn't assume any consecutive words in a text will form an acronym that you should match against.

Take a look at your opening post. Do you think a search engine should match it when someone searches for ETF? Well, your post has the phrase "expect the following" in it. And what if someone searches for animal nutrition? The word "an" appears as well.

You should first define how you want searches to work, exactly. "Like Google" isn't realistic. But if you feel it's appropriate, you could integrate Google's Programmable Search Engine to your website and limit searches to the post pages on your site. Then you could take advantage of their search engine, as long as the pages have been fully indexed by Google.

Please or to participate in this conversation.