10 SQL statement optimization techniques to improve MYSQL query efficiency, mysqlsql

10 SQL statement optimization techniques to improve MYSQL query efficiency, mysqlsql

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

1. Optimize Your MySQL query Cache

You can enable the High-Speed query cache for queries on the MySQL server. It is one of the most effective ways to improve performance by quietly processing the database engine in the background. When the same query is executed multiple times, if the result is extracted from the cache, it is quite fast.
But the main problem is that it is so easy to hide that most of our programmers will 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 query clearer

Using the EXPLAIN keyword is another MySQL optimization technique that helps you understand what MySQL is performing for query operations. This helps you find the bottleneck, it also shows where the query or table structure is faulty.

The EXPLAIN query results can tell you how indexes are being referenced, and how tables are scanned and sorted.

Implement a SELECT query (preferably a complicated one with the joins method). 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 executing joins, the explanation can help me locate the problem.

3. Use LIMIT 1 to get a unique row

Sometimes, when you want to query a table, you know that you only need to read a row. You may go to a very unique record, or just check the number of any existing records. They all satisfy your WHERE clause.

In this case, addLIMIT 1This will make your query more effective. In this way, the database engine will only stop scanning after 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

An index is not only a primary key or a unique key. If you want to search for any column in the table, you should always point to the index.

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

If the application contains multiple connection queries, make sure that the linked columns are indexed on both tables. This affects how MySQL optimizes the internal join operation.

In addition, the columns to be added must be of the same type. For example, if you add a DECIMAL column and an int column to another table, MySQL cannot use at least one of the indexes. 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 new programmers will fall. You may have created a terrible calm without knowing it. This trap is created when you use the by rand () command.

If you really need to display your results randomly, there are many better ways to achieve it. It is true that this requires more code, but it can avoid performance bottlenecks. The problem is that MySQL may execute the by rand () command for each independent row in the table (which consumes the processing power of the processor) and then return only one row for 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 the SELECT * command whenever possible

The more data you read from a table, the slower the query. It increases the disk operation time, or when the database server and the WEB server are independent. You will experience a very long network latency, just because the data is not required to be transmitted between servers. It is a 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's Column Structure Analysis and actual data in the table to give you some suggestions. If the actual data already exists in your table, it can serve your major decision-making.

9. Prepared statements

The prepared statements can be helpful in performance optimization and security.

By default, prepared statements filter Bound variables to effectively protect applications and prevent SQL injection attacks. Of course, you can also manually filter data, but most programmers are hard to achieve results due to their forgetful character.

// 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 the IP address as an unsigned integer.

When creating a VARCHAR (15), many programmers do not realize that they can store IP addresses as integers. When you have an INT type, you only occupy 4 bytes of space, which is a fixed size field.
You must make sure that the column you operate on is of the unsigned int type, 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 MYSQL query statements. Let's talk about these 10 types first today.