Pular para o conteúdo principal

SQL: Never uses LIKE unless stricted necessary.


Demand

MUST use "=" (equals sign) instead "LIKE" (like command) whenever "%" (percent sign) is NOT necessary

Description

In MySQL, the "LIKE" operator behaves similarly to "=" (equals sign) when the "%" (percent sign) wildcard is not utilized.

Employing "LIKE" in most of the modern SGDBs (e.q. MySQL) results in the use of a more complex query structures.

The examples below illustrate this. The first query does not use the "%" modifier and is compared to the second, which does. Both provide the same search structure. Contrasting these with the last query, which uses only "=", results in a simpler search structure and all have the same output result.

Therefore, if the "%" modifier is not being used in the search, it's preferable to utilize "=" for simpler and more efficient query execution.

Examples

1:  explain SELECT COUNT(1) FROM users WHERE `username` like "mke";  
2:  # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
3:  # 1, SIMPLE, users, , range, username, username, 82, , 1, 100.00, Using where; Using index
1: explain SELECT COUNT(1) FROM users WHERE `username` like "mke%";   
2: # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
3: # 1, SIMPLE, users, , range, username, username, 82, , 1, 100.00, Using where; Using index

1: explain SELECT COUNT(1) FROM users WHERE `username` = "mke";
2: # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
3: 1, SIMPLE, , , , , , , , , , no matching row in const tableExamples explanation
  1. The query with WHERE username LIKE "mke": This uses a "range" type and applies the index to the search.
  2. The query with WHERE username LIKE "mke%": This also utilizes a "range" type but has a higher number of rows scanned due to the use of the "%" modifier.
  3. The query with WHERE username = "mke": This employs a "const" type and directly uses the index for a more straightforward search.
Marcadores:
Local: Nova Iorque, NY, EUA

Comentários

Postar um comentário

Postagens mais visitadas deste blog

Demystifying MySQL ALTER TABLE ALGORITHM: INPLACE vs INSTANT vs COPY

  Schema evolution is one of the trickiest aspects of database management. For developers and DBAs, the dreaded ALTER TABLE command often conjures fears of downtime, locks, and performance bottlenecks. But MySQL offers a powerful word! The "ALGORITHM" modifier that determines how schema changes are executed. In this article, we’ll break down the three key algorithms "INPLACE", "INSTANT", and "COPY" to help you understand their trade-offs, performance implications, and even some unconventional ways to use them. Whether you’re optimizing for zero downtime or experimenting with stress testing, this guide will give you practical insights and real-world strategies. IMPORTANT: Check your MySQL version: In MySQL 8.0+, many table changes are instantaneous, and the command modifications presented here are only available in this and later versions. Understanding the ALGORITHM Modifier When you run an ALTER TABLE in MySQL, you can specify the algorithm, lik...
Postar um comentário
Leia mais

Article: Preventing Database Gridlock: Recognizing and Resolving Deadlock Scenarios

Learn how deadlocks occur in database systems, understand their impact on performance, and discover practical techniques for identifying potential deadlock scenarios in your SQL code.
Postar um comentário
Leia mais

Collation Confusion: Demystifying VARBINARY vs VARCHAR in Database Design

Have you ever wondered why your database sorts strings differently than expected, or why a seemingly simple query delivers "quirky" results? If you’ve worked with databases like MySQL, PostgreSQL, or SQL Server, you’ve likely stumbled across the mysterious world of collation and the subtle but critical differences between VARBINARY and VARCHAR data types. In this article I hope to unravel these concepts for you, explore how collation shapes database behavior, and dive into an experimental case study to reveal surprising insights. Whether you’re a developer or a database engineer, this deep dive will equip you with practical knowledge to avoid common pitfalls and optimize your database designs. Let’s get started! What Is Collation and Why Does It Matter? Collation refers to the set of rules a database uses to compare and sort character strings. It governs how strings are ordered (e.g., is “Apple” less than “apple”?), how accents are treated (e.g., does “é” equal “e”?), and eve...
Postar um comentário
Leia mais