Top Ten MySQL optimization tips

Web developers not only need to solve the efficiency of the program, the rapid access to the database and the corresponding is also a big problem. I hope this article will help you master MySQL optimization techniques.

1. Optimize your MySQL query cache

Queries on the MySQL server enable high speed query caching. Keeping the database engine in the background quietly is one of the most effective ways to improve performance. When the same query is executed multiple times, if the result is extracted from the cache, it is fairly 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 from working.

1.//query cache does not work

2. $r = mysql_query ("Select username from user WHERE signup_date >= curdate ()");

3.

4.//Query Cache works!

5. $today = Date ("y-m-d");

6. $r = mysql_query ("Select username from user WHERE signup_date >= ' $today '");

7.

8.//query cache does not work

9. $r = mysql_query ("Select username from user WHERE signup_date >= curdate ()");

10.

One.//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 lets you know what kind of query MySQL is doing, which can help you find out where the bottleneck is, and show where the query or table structure is going wrong.

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

Implement a select query (preferably a more complex one, with joins), add your keyword explanation here, where we can use phpMyAdmin and he will tell you the results in the table. For example, if I'm forgetting to add a column to an index while I'm performing joins, explain can help me find out where the problem is.

After adding an index to group_id field

3. Use limit 1 to get a unique line

Sometimes, when you want to query a table, you know you just have to look at one line. You may go to a very unique record, or just check the number of records that exist, and they all satisfy your WHERE clause.

In this case, adding a limit 1 will make your query more efficient. This allows the database engine to discover that only 1 will stop the scan, rather than scan the entire table or index.

1.//do I have any of the users from Alabama?

2.//What DO:

3. $r = mysql_query ("SELECT * from user WHERE state = ' Alabama '");

4. if (mysql_num_rows ($r) > 0) {

5.//...

6.}

7.//Much better:

8. $r = mysql_query ("Select 1 from user WHERE state = ' Alabama ' LIMIT 1");

9. if (mysql_num_rows ($r) > 0) {

10.//...

11.}

4. Retrieving fields in the index

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

5. Ensure that the index of the connection is the same type

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

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

1.//Looking for companies in I state

2. $r = mysql_query ("Select Company_Name from Users

3. Left JOIN companies on (users.state = companies.state)

4. WHERE users.id = $user _id ");

5.

6.//Both State columns should is indexed

7.//And they both should be the same type and character encoding

8.//or MySQL might do full table scans

6. Do not use the by RAND () command

This is a trap that many novice programmers fall into. You may have unknowingly created a terrible calm. This trap was created when you were using the by RAND () command.

If you really need to show your results randomly, there are a lot of better ways to do that. Admittedly, this requires writing more code, but it avoids the appearance of performance bottlenecks. The problem is that MySQL may execute by the RAND () command for each individual row in the table (which consumes the processor's processing power), and then give you just one line to return.

1.//What DO:

2. $r = mysql_query ("Select username from user order by RAND () LIMIT 1");

3.//Much better:

4. $r = mysql_query ("SELECT count (*) from user");

5. $d = Mysql_fetch_row ($r);

6. $rand = Mt_rand (0, $d [0]-1);

7.

8. $r = mysql_query ("Select username from user LIMIT $rand, 1");

7. Try to avoid select * command

The more data you read from a table, the more slowly the query becomes. He increases the time that the disk needs to be operated on, or when the database server is separate from the Web server. You will experience a very long network delay, simply because data is not required to be transferred between servers.

Always specify the columns you need, which is a very good habit.

1.//Not preferred

2. $r = mysql_query ("SELECT * from user WHERE user_id = 1");

3. $d = Mysql_fetch_assoc ($r);

4. Echo "Welcome {$d [' username ']}";

5.//Better:

6. $r = mysql_query ("Select username from user WHERE user_id = 1");

7. $d = Mysql_fetch_assoc ($r);

8. Echo "Welcome {$d [' username ']}";

9.//The differences are significant with bigger result sets

8. Advice received from procedure analyse ()

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

9. Prepared statements

Prepared statements can help you with both performance optimization and security.

Prepared statements filter the variables that are already bound by default, can provide effective protection to the application and prevent SQL injection attacks. Of course, you can also manually filter, but because most programmers forgetful personality, it is difficult to achieve results.

1.//Create a prepared statement

2. if ($stmt = $mysqli->prepare ("Select username from user WHERE state=?")) {

3.//Bind parameters

4. $stmt->bind_param ("s", $state);

5.//Execute

6. $stmt->execute ();

7.//Bind result variables

8. $stmt->bind_result ($username);

9.//Fetch value

$stmt->fetch ();

printf ("%s is from%sn", $username, $state);

$stmt->close ();

13.}

10. Store IP address as unsigned integral type

Many programmers create a varchar (15) without realizing that they can store the IP address in integer form. 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 are manipulating is a unsigned int type, because the IP address will use the 32-bit unsigned integer.

1. $r = "UPDATE users SET IP = Inet_aton (' {$_server[' remote_addr ']} ') WHERE user_id = $user _id";

The top ten MySQL optimization techniques are introduced here.