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

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

PHP: Always use an index to add a values to an array

  Demand Always identify the array index when manipulating the array directly Description Whenever you are adding a new item to the array you must add an index so that php does not need to recalculate the size of the array to identify in which position the new content will be added. Examples 1: # Bad code: 2: do { 3: $array[] = $newItem; 4: } while(!feof($fp)); 5: 6: # Good code 7: $array = []; 8: $index = 0; 9: do { 10: $array[$index++] = $newItem; 11: } while(!feof($fp)); Examples Explanation When instantiating a new item in an array in the "bad example" php will identify that you expect the item to be added to the end of the array, since array is a primitive type and has no attributes php always needs to call a calculation function to identify the current size of the array before trying to add a new item to the end of it. By monitoring the growth of the array in an external variable and automatically reference which position will...
Postar um comentário
Leia mais

General: Prevent arbitrary precision arithmetic

Demand Some simple decimal calculations like 4.6 * 100 may lead in irregularities when dealing with numbers. Description During arithmetic operations like addition, subtraction, multiplication, and division, the numbers are manipulated according to the rules defined by the IEEE 754 standard. However, due to the finite precision of the representation, rounding errors may occur, leading to small discrepancies between the expected and actual results of computations. In simpler terms, the computer operates using powers of 2 (binary-based), so whenever we need to work with a number that cannot be exactly represented in binary (like 0.1, which is a base-10 fraction), it uses an approximation rather than an infinite number and is incapable of presenting the exact correct value. To prevent it we may use some libraries to prevent the problem. In Javascript and Typescript we have the BigNumbers library (and others). PHP has a built-in library for arbitrary-precision arithmetic called BCMath (Bin...
Postar um comentário
Leia mais