Demystifying the “LIKE” Operator and Database Query Logics
If you are just getting started with relational databases, writing your first search query can feel like trying to speak a foreign language. The syntax seems bizarre, the errors are cryptic, and there are multiple ways to retrieve the exact same piece of data.
However, behind the technical curtain, database search queries operate on simple, logical rules. If you can learn how a single SQL command—namely the LIKE operator—interacts with wildcard characters, you will instantly unlock the ability to search through thousands of records with precision.
In this guide, we are going to break down the fundamentals of database-driven searching. We will explain how the most basic SQL search scripts work, write custom queries, and discuss the real-world limitations you will face as your database grows.
The Heart of Simple Searching: The SELECT Query
At its core, a search query is simply a request for information from a database table. In SQL, this request starts with the SELECT statement.
Imagine you have a table called users containing usernames, email addresses, and country codes. If you want to see everyone in your database, you write:
SQL
SELECT * FROM users;
The asterisk (*) is a wildcard that means “give me every single column.”
But in a search engine, we do not want to see everything. We want to filter the results based on what the user types in. To do this, we add a conditional filter using the WHERE clause:
SQL
SELECT * FROM users WHERE country = 'Poland';
This works perfectly when you are looking for an exact match. But what happens if you want to find a user, and you only remember that their name starts with the letter “J”? An exact match won’t help you here.
Introducing the SQL “LIKE” Operator and Wildcards
To perform flexible, partial matches in SQL, we use the LIKE operator combined with specific wildcard characters. In MySQL, the percentage symbol (%) acts as a placeholder for any number of characters (including zero characters).
Let’s look at three different ways you can use the % wildcard to search:
1. The Prefix Search (Starts With…)
If you want to find all users whose names start with “Mar” (such as Mary, Mark, or Marcus), you place the wildcard at the end of the query:
SQL
SELECT * FROM users WHERE name LIKE 'Mar%';
2. The Suffix Search (Ends With…)
If you want to find users whose email addresses end with a specific domain, you place the wildcard at the beginning of the query:
SQL
SELECT * FROM users WHERE email LIKE '%@gmail.com';
3. The Containment Search (Contains anywhere…)
This is the most common pattern for generic search engines. If you want to find any article that mentions the word “coffee” anywhere in the title, you wrap the keyword in wildcards on both sides:
SQL
SELECT * FROM users WHERE bio LIKE '%coffee%';
Combining Filters with AND and OR
A great search engine doesn’t just look at one column. If a user searches for “Smith,” you probably want to search both the first_name and last_name columns. You can combine these conditions using the OR operator:
SQL
SELECT * FROM users WHERE first_name LIKE '%Smith%' OR last_name LIKE '%Smith%';
If you need to make your search highly specific, you can use AND. For example, to find users named “Smith” who live specifically in “Poland,” you would write:
SQL
SELECT * FROM users WHERE (last_name LIKE '%Smith%') AND (country = 'Poland');
The Performance Catch: Why Simple Search Slows Down
While the LIKE operator is incredibly easy to understand and write, it comes with a massive hidden cost: performance.
Relational databases use “indexes” to find data quickly. Think of an index like an index at the back of a physical book. If you want to find a topic, you check the index first, find the page number, and flip straight there.
However, when you write a query containing a wildcard at the very beginning (like LIKE '%coffee%'), the database engine cannot use its index. Why? Because the word could start with literally anything.
Consequently, the database is forced to perform a Full Table Scan. It must read every single row in your table from top to bottom, manually checking if the string “coffee” exists inside.
For a small site with 500 records, this happens in a fraction of a millisecond. But if your database grows to 500,000 rows, a full table scan can take several seconds, clogging your server and causing your website to load at a crawl.
Where to Go From Here?
For small, custom projects, internal dashboards, and prototypes, a simple SQL script using PDO and the LIKE operator is absolutely fine. It is simple to implement, extremely robust, and highly predictable.
But as your site starts to scale, you will need to graduate to more advanced techniques—such as SQL Full-Text Search or specialized third-party search indexes. Understanding these basic building blocks, however, is the essential first step to mastering database architecture.
Leave a Reply