Implementing Keyword Search in SQL

1. Introduction

Keyword search is a fundamental feature in many applications, enabling users to locate specific information quickly. In SQL, we can implement keyword search using tools such as the LIKE operator for simple searches or full-text indexing for more advanced requirements.

In this tutorial, we’ll explore how to achieve keyword search using SQL queries. We’ll focus on three popular relational databases: PostgreSQL, MySQL, and SQL Server.

To illustrate, we’ll use the Course table from the Baeldung University database.

2. Keyword Search in SQL

We can use both the LIKE operator and full-text search techniques to implement keyword search. The LIKE operator is best for simple pattern-matching queries, while full-text search is more suited for complex queries involving large datasets and advanced linguistic analysis. Furthermore, choosing between them depends on data size, query complexity, and performance requirements.

Let’s begin with the use of the LIKE operator.

2.1. Using the LIKE Operator

For basic keyword searches, the LIKE operator is straightforward and effective. For example, let’s find courses with names containing the keyword Operating:

SELECT id, name, textbook, credits
FROM Course
WHERE name LIKE '%Operating%';
+-------+---------------------------------------------+-----------------------------------------------------------+---------+
| id    | name                                        | textbook                                                  | credits |
|-------+---------------------------------------------+-----------------------------------------------------------+---------|
| CS111 | Introduction to Operating Systems           | OS by Tanenbaum                                           | 7       |
| CS112 | Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling | 7       |
| CS211 | Operating Systems: Intermediate             | OS by Tanenbaum                                           | 7       |
...
SELECT 5
Time: 0.005s

This query searches for any course name that includes the word Operating. The % symbol acts as a wildcard that matches any sequence of characters before or after the keyword.

Furthermore, we can expand the search to include multiple columns. For example, let’s search in both the name and textbook columns in the Course table:

SELECT id, name, textbook, credits
FROM Course
WHERE name LIKE '%Database Systems%' OR textbook LIKE '%Database Systems%';
+-------+-------------------------------------------+-------------------------------------+---------+
| id    | name                                      | textbook                            | credits |
|-------+-------------------------------------------+-------------------------------------+---------|
| CS121 | Introduction to Databases                 | Database Systems: The Complete Book | 7       |
| CS122 | Relational Databases                      | Database Systems: The Complete Book | 7       |
| CS123 | Introduction to Structured Query Language | Database Systems: The Complete Book | 7       |
...
SELECT 6
Time: 0.007s

As seen in the output, the query returns any course that includes “Database Systems” in either the name or textbook column.

2.2. Using Full-Text Search in PostgreSQL

For advanced scenarios, we can use PostgreSQL’s built-in full-text search functionality. Particularly, we use this method when handling large datasets, complex queries, or tasks requiring linguistic processing like stemming and ranking.

Let’s assume we want the name and textbook columns to be indexed for full-text search. We can create a tsvector column and index it:

-- Add a tsvector column for full-text search
ALTER TABLE Course ADD COLUMN search_vector tsvector;

-- Populate the tsvector column with data from the 'name' and 'textbook' columns
UPDATE Course SET search_vector =
    to_tsvector('english', name || ' ' || textbook);

-- Create a GIN index on the search_vector column for faster querying
CREATE INDEX idx_course_search ON Course USING GIN (search_vector);
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
ALTER TABLE
Time: 0.019s
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
UPDATE 81
Time: 0.027s
CREATE INDEX
Time: 0.005s

After setting up search_vector, we can perform full-text search queries. For example, let’s search for courses related to Operating Systems:

SELECT id, name, textbook, credits
FROM Course
WHERE search_vector @@ to_tsquery('Operating & Systems');
+-------+---------------------------------------------+-----------------------------------------------------------+---------+
| id    | name                                        | textbook                                                  | credits |
|-------+---------------------------------------------+-----------------------------------------------------------+---------|
| CS111 | Introduction to Operating Systems           | OS by Tanenbaum                                           | 7       |
| CS112 | Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling | 7       |
| CS211 | Operating Systems: Intermediate             | OS by Tanenbaum                                           | 7       |
...
SELECT 5
Time: 0.007s

The to_tsquery function converts the input string into a format suitable for full-text search. Then, the @@ operator checks if the search_vector matches the query.

2.3. Using Full-Text Search in MySQL

For advanced keyword search, MySQL supports full-text indexing in MyISAM and InnoDB tables. Let’s create a full-text index on the name and textbook columns:

ALTER TABLE Course ADD FULLTEXT(name, textbook);
Query OK, 0 rows affected, 1 warning (0.057 sec)
Records: 0  Duplicates: 0  Warnings: 1

Once the full-text index is created, we can use the MATCH() function to perform keyword searches. For example, let’s find courses related to Operating Systems:

SELECT id, name, textbook, credits
FROM Course
WHERE MATCH(name, textbook) AGAINST('Operating Systems');
+-------+---------------------------------------------+------------------------------------------------------------------------+---------+
| id    | name                                        | textbook                                                               | credits |
+-------+---------------------------------------------+------------------------------------------------------------------------+---------+
| CS112 | Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling              |       7 |
| CS212 | Real Time Operating Systems: Intermediate   | Real-Time Operating Systems Book 1: The Theory by Cooling              |       7 |
| CS111 | Introduction to Operating Systems           | OS by Tanenbaum                                                        |       7 |
| CS211 | Operating Systems: Intermediate             | OS by Tanenbaum                                                        |       7 |
...
15 rows in set (0.005 sec)

By default, MySQL full-text search operates in natural language mode, which considers the relevance of each keyword.

2.4. Using Full-Text Search in SQL Server

SQL Server provides a robust full-text search feature. To use it, we must first enable full-text indexing on the database and table. Let’s enable full-text indexing and also create a full-text index on the name and textbook columns:

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Course(name, textbook)
KEY INDEX PK_Course;
(1 row affected)
(1 row affected)

Once the full-text index is created, we can perform keyword searches using the CONTAINS() function:

SELECT id, name, textbook, credits
FROM Course
WHERE CONTAINS((name, textbook), 'Operating AND Systems');
+-------+---------------------------------------------+------------------------------------------------------------------------+---------+
| id    | name                                        | textbook                                                               | credits |
+-------+---------------------------------------------+------------------------------------------------------------------------+---------+
| CS112 | Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling              |       7 |
| CS212 | Real Time Operating Systems: Intermediate   | Real-Time Operating Systems Book 1: The Theory by Cooling              |       7 |
| CS111 | Introduction to Operating Systems           | OS by Tanenbaum                                                        |       7 |
...

The CONTAINS() function allows complex queries with logical operators like AND, OR, and NEAR.

3. Conclusion

In this article, we’ve explored various methods to implement keyword search in PostgreSQL, MySQL, and SQL Server. We demonstrated how each database offers unique tools tailored to different use cases.

By leveraging these techniques, we can efficiently query and retrieve data, ensuring optimal performance and accurate results.