56 SQL Queries That Will Destroy Your Database (Part 3 of 56)

This is Query #4 in the series, and it focuses on a common performance issue in text search: using a leading wildcard in a LIKE condition.

In SQL, the LIKE operator is used to search for patterns inside text columns. However, the way you place wildcards (%) in the pattern can have a big impact on performance, especially when indexes are involved.

The dangerous query

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE '%smith';

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE '%smith';

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE '%smith';

Why is this dangerous

When the database sees this condition:

%smith

%smith

%smith

it means:

“Find any customer name that ends with 'smith', no matter what comes before it.”

The problem is the leading % wildcard.

Because the database does not know where the text starts, it cannot use the index on customer_name.

Instead, it may:

• Scan every row in the table

• Check each value one by one

• Compare the ending of every string

This is called a full table scan, and it becomes very slow as the dataset grows.

In small datasets, this might not be noticeable. But in production systems with large tables, it can significantly impact query performance.

The fixed version

To allow index usage, we change the pattern so the search starts from the beginning of the string:

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE 'smith%';

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE 'smith%';

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE 'smith%';

Now the database can:

• Use the index on customer_name

• Jump directly to values starting with “smith”

• Stop scanning once matching rows end

This makes the query much more efficient.

When this still doesn’t solve the problem

If you need to search for a word anywhere inside a string (not just at the beginning), like:

%smith

%smith

%smith

then a normal index will still not help.

In that case, you should use a full-text search index, which is designed specifically for searching words inside large text fields.

One-line rule

Always avoid leading wildcards in LIKE conditions, because they prevent index usage and force full table scans.