Support FullText search

[kasramp]

Update the Book table:

ALTER TABLE book ADD FULLTEXT(title, author);

And query:

@Query(value = """
  SELECT b.id AS id,
         b.title AS title,
         b.author AS author,
         b.cover_image_url AS coverImageUrl,
         MATCH(b.title, b.author) AGAINST(:query IN NATURAL LANGUAGE MODE) AS relevance
  FROM book b
  WHERE b.provider = :provider
    AND MATCH(b.title, b.author) AGAINST(:query IN NATURAL LANGUAGE MODE)
  ORDER BY relevance DESC
  """,
  nativeQuery = true)
List<BookOverview> searchBooksByRelevance(@Param("provider") String provider,
                                          @Param("query") String query,
                                          Pageable pageable);

The drawback is that, it doesn't do partial search and is typo intolerant.

The issue with the Like %phrase% is that it doesn't use index and if the table gets too large, it becomes too slow.