Skip to content

Instantly share code, notes, and snippets.

@xaprb

xaprb/fts.sql Secret

Created December 14, 2013 20:04
Show Gist options
  • Save xaprb/18c5b9d70a21d531acd0 to your computer and use it in GitHub Desktop.
Save xaprb/18c5b9d70a21d531acd0 to your computer and use it in GitHub Desktop.
Various fulltext search query examples
SELECT film_id, title, RIGHT(description, 25),
MATCH(title, description) AGAINST('factory casualties') AS relevance
FROM sakila.film_text
WHERE MATCH(title, description) AGAINST('factory casualties');
SELECT film_id, RIGHT(description, 25),
ROUND(MATCH(title, description) AGAINST('factory casualties'), 3)
AS full_rel,
ROUND(MATCH(title) AGAINST('factory casualties'), 3) AS title_rel
FROM sakila.film_text
WHERE MATCH(title, description) AGAINST('factory casualties')
ORDER BY (2 * MATCH(title) AGAINST('factory casualties'))
+ MATCH(title, description) AGAINST('factory casualties') DESC;
SELECT film_id, title, RIGHT(description, 25)
FROM sakila.film_text
WHERE MATCH(title, description)
AGAINST('+factory +casualties' IN BOOLEAN MODE);
SELECT film_id, title, RIGHT(description, 25)
FROM sakila.film_text
WHERE MATCH(title, description)
AGAINST('"spirited casualties"' IN BOOLEAN MODE);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment