-- Create a FULLTEXT index (MyISAM/InnoDB as supported)
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200),
body TEXT,
FULLTEXT(title, body)
);
-- Query
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('+database +scaling' IN BOOLEAN MODE);
-- Enable full-text on a table
CREATE TABLE Articles (
Id INT PRIMARY KEY,
Title NVARCHAR(200),
Body NVARCHAR(MAX)
);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON Articles(Title, Body) KEY INDEX PK__Articles__Id;
-- Query
SELECT Id, Title
FROM Articles
WHERE CONTAINS((Title, Body), '"database" AND "scaling"');
-- SQLite FTS5 (if compiled)
CREATE VIRTUAL TABLE articles USING fts5(title, body);
INSERT INTO articles(title, body) VALUES ('DB Tuning', 'database performance scaling tips'),
('Intro', 'hello world');
SELECT rowid, title FROM articles WHERE articles MATCH 'database AND scaling';