10 SQL statement optimization techniques to improve MYSQL query efficiency

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

1. Optimize your MySQL query cache

When querying on a MySQL server, you can enable a high-speed query cache. Letting the database engine do its work quietly in the background is one of the most effective ways to improve performance. When the same query is executed multiple times, it is quite fast if the results are fetched from cache.
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 the query cache from working.

// query cache does NOT work

$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); 
// query cache works! 
$today = date("Ymd"); 
$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("Ymd"); 
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. Make your SELECT queries clearer with EXPLAIN

Using the EXPLAIN keyword is another MySQL optimization tip that lets you see what kind of query operations MySQL is performing. This can help you find bottlenecks and show you where there are problems with the query or table structure.

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

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

3. Use LIMIT 1 to get the only row

Sometimes, when you query a table, you know you only need to look at one row. You might be looking for a very unique record, or just checking for any number of records that exist that satisfy your WHERE clause.

In this case, adding a LIMIT 1 will make your query more efficient. This way the database engine will stop scanning after finding only 1, instead of scanning the entire table or index.

// 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. Search fields in the index

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

5. Ensure that the indexes being joined are of the same type

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

In addition, the columns being added must be of the same type. For example, if you join a DECIMAL column and at the same time join an int column from another table, MySQL will not be able to use at least one of the indices. Even the character encoding must be the same as the 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. Don’t use BY RAND() command

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

If you really need to randomize your results, there are much better ways to do it. Granted, this requires writing more code, but it avoids performance bottlenecks. The problem is that MySQL may execute the BY RAND() command for each individual row in the table (which consumes processor power) and then return only one row to you.

// 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. Avoid SELECT * commands

The more data you read from the table, the slower the query will be. It increases the time required for disk operation, especially when the database server and the web server are separated. You will experience very long network delays simply because data is traveling between servers unnecessarily. It is a very good habit to always specify the columns you need.

// 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 suggestions from PROCEDURE ANALYSE()

PROCEDURE ANALYSE() allows MySQL to analyze the column structure and the actual data in the table to give you some suggestions. If you already have actual data in your table, it can help you make important decisions.

9. Prepared Statements

Prepared statements can help you in terms of performance optimization and security.

Prepared statements filter already bound variables by default, giving applications effective protection against SQL injection attacks. Of course you can also filter manually, but due to the forgetful nature of most programmers, it is difficult to achieve the desired effect.

// 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. Storing IP addresses as unsigned integers

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