10 SQL statement optimization skills to improve MYSQL query efficiency

MySQL database execution efficiency has a great impact on the execution speed of the program, effective processing optimization database is very useful. Especially when a large amount of data needs to be processed.

1. Optimize your MySQL query cache

Queries on an MySQL server enable high-speed query caching. One of the most effective ways to improve performance is to let the database engine process quietly in the background. When the same query is executed multiple times, if the result is fetched from the cache, it is quite fast.
But the main problem is that it is so easily hidden that most of us programmers ignore it. In some processing tasks, we can actually prevent query caching.

// query cache does NOT work

$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); 
// query cache works! 
$today = date("Y-m-d"); 
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'"); 
// query cache does NOT work 
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); 
// query cache works! 
$today = date("Y-m-d"); 
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. Use EXPLAIN to make your SELECT queries clearer

Using EXPLAIN keyword is another MySQL optimization technique, which can let you know what kind of query operation MySQL is doing, which can help you find the bottleneck and show where the query or table structure is wrong.

The results of the EXPLAIN query can tell you which indexes are being referenced, how tables are being scanned and sorted, and so on.

Realize an SELECT query (preferably a more complex one, with joins mode), add your keyword explanation in it, here we can use phpMyAdmin, he will tell you the results in the table. For example, if I forget to add a column to an index while executing joins, EXPLAIN can help me find the problem.

3. Use LIMIT 1 to get only 1 row

Sometimes, when you want to query a table, you know that you only need to look at one row. You may go to a 10-point unique record, or just check for any number of records that exist, and they all meet your WHERE clause.

In this case, adding an LIMIT 1 will make your query more efficient. Instead of scanning the entire table or index, the Database Engine will stop scanning when it finds that there is only 1.

// do I have any users from Alabama? 
// what NOT to do: 
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'"); 
if (mysql_num_rows($r) > 0) { 
 // ... 
}  
// much better: 
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1"); 
if (mysql_num_rows($r) > 0) { 
 // ... 
}

4. Retrieval Fields in an Index

Indexes are not only primary keys or only 1 keys. If you want to search any column in the table, you should point 1 directly to the index.

5. Ensure that the indexes of the joins are of the same type

If your application contains multiple join queries, you need to make sure that the columns you link are indexed on both sides of the table. This affects how MySQL optimizes inner join operations.

In addition, the added column must be of the same type 1. For example, if you add an DECIMAL column and add an int column in another table at the same time, MySQL will not be able to use at least one of the metrics. Even if the character encoding must be of the same string type.

// looking for companies in my state 
$r = mysql_query("SELECT company_name FROM users 
 LEFT JOIN companies ON (users.state = companies.state) 
 WHERE users.id = $user_id"); 
// both state columns should be indexed 

// and they both should be the same type and character encoding 

// or MySQL might do full table scans

6. Do not use the BY RAND () command

This is a trap that many novice programmers will fall into. You may have unconsciously created a terrible calm. This trap is created when you use the BY RAND () command.

If you really need to randomly display your results, there are many better ways to do so. Admittedly, this requires more code to be written, but it can avoid performance bottlenecks. The problem is that MySQL might execute the BY RAND () command for every single row in the table (which consumes processor power) and then return you only one row.

// what NOT to do: 
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1"); 
// much better: 
$r = mysql_query("SELECT count(*) FROM user"); 
$d = mysql_fetch_row($r); 
$rand = mt_rand(0,$d[0] - 1); 
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. Try to avoid the SELECT * command

The more data you read from the table, the slower the query becomes. It increases the amount of time the disk needs to operate, or if the database server is separate from the WEB server. You will experience very long network latency simply because data is unnecessarily transferred between servers. Always specify the columns you need, which is a very good habit.

// not preferred 
$r = mysql_query("SELECT * FROM user WHERE user_id = 1"); 
$d = mysql_fetch_assoc($r); 
echo "Welcome {$d['username']}"; 
// better: 
$r = mysql_query("SELECT username FROM user WHERE user_id = 1"); 
$d = mysql_fetch_assoc($r); 
echo "Welcome {$d['username']}"; 
// the differences are more significant with bigger result sets

8. Get advice from PROCEDURE ANALYSE ()

PROCEDURE ANALYSE () allows the column structure analysis of MySQL and the actual data in the table to give you some advice. If you already have actual data in your table, it can serve your major decisions.

9. Prepared statements

Prepared statements can be helpful to everyone from two aspects of performance optimization and security.

Prepared statements in filtering already bound variables by default can give the application with effective protection against SQL injection attacks. Of course, you can also filter manually, but because most programmers are forgetful, it is difficult to achieve results.

// create a prepared statement 
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) { 
 // bind parameters 
 $stmt->bind_param("s", $state); 
 // execute 
 $stmt->execute(); 
 // bind result variables 
 $stmt->bind_result($username);  
 // fetch value 
 $stmt->fetch(); 
 printf("%s is from %s\n", $username, $state);  
 $stmt->close(); 
}

10. Store IP addresses as unsigned integers

Many programmers create an VARCHAR (15) without realizing that they can store IP addresses as integers. When you have an INT type, you only take up 4 bytes of space, which is a fixed-size field.
You must make sure that the column you are working on is of type 1 UNSIGNED INT, because the IP address will use 32-bit unsigned integer.
1. $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
There are still many query statements in MYSQL. Today, we will talk about these 10 kinds first