Introduction
In modern applications, efficient text search is crucial, especially when dealing with large databases. While MySQL provides basic full-text search capabilities, it falls short when it comes to fuzzy matching or handling misspellings. This is where trigram-based search comes into play. In this blog, we’ll explore what a trigram is, how it improves search performance, and how you can implement trigram search in MySQL.

What is a Trigram?
A trigram is a sequence of three consecutive characters from a given string. For instance, the word "search" can be broken down into the following trigrams:

• sea
• ear
• arc
• rch By breaking down words into trigrams, we can perform more flexible and efficient text matching, especially when trying to match incomplete or slightly misspelled terms.

Implementing Trigram Search in MySQL By Creating Temporary Table

1.Create Trigram Function in MySQL database. Trigram function code:

CREATE FUNCTION TRIGRAM_SEARCH(search_string VARCHAR(255), target_string VARCHAR(255)) 
RETURNS FLOAT
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total_trigrams INT DEFAULT 0;
    DECLARE matched_trigrams INT DEFAULT 0;
    DECLARE search_length INT;
    DECLARE target_length INT;

    SET search_length = CHAR_LENGTH(search_string);
    SET target_length = CHAR_LENGTH(target_string);

    -- Handle edge cases where strings are too short
    IF search_length  0 THEN
        RETURN matched_trigrams / total_trigrams;
    ELSE
        RETURN 0;
    END IF;
END;

2.Now Indexing Desired Column to full-text

@Entity()
@Index(['title'], { fulltext: true })
export class Ebook extends BaseEntity {
  @PrimaryGeneratedColumn()
  ebookId: number;

  @Column({ nullable: true })
  title: string;
}

3.Testing of Trigram Search Function

select *
FROM ebook e 
WHERE TRIGRAM_SEARCH('physis onlu', e.title) > 0.4
ORDER BY TRIGRAM_SEARCH('physis onlu', e.title) desc;

4.Implement trigram search in code

 async find(title?: string) {
        const eBooks = await this.dataSource
      .getRepository(Ebook)
      .createQueryBuilder('eBook');
    if (title) {
      eBooks.where(`TRIGRAM_SEARCH(:title, eBook.title) > 0.4`, { title });
    }
    const result = await eBooks.getMany();
    return result;
  }

Conclusion
Trigram search offers a powerful way to implement fuzzy matching in MySQL databases. By breaking down text into trigrams, we can perform more flexible and forgiving searches, greatly enhancing the user experience in applications where text search is crucial.
While this approach has its strengths, it's important to consider alternatives like Levenshtein distance or soundex algorithms depending on your specific use case and performance requirements.
By implementing trigram search, you can significantly improve the search capabilities of your Node.js and MySQL applications, providing users with more intelligent and forgiving search results.