MySQL Ten optimization tips

1. Optimize your MySQL query cache

Querying on the MySQL server enables high-speed query caching. Having the database engine quietly handled in the background is one of the most effective ways to improve performance. When the same query is executed multiple times, it is fairly fast if the result is extracted from the cache.

But the main problem is that it is so easily hidden that most of our programmers will ignore it. In some processing tasks, we can actually prevent the query cache from working.

//Query Cache does not  Work$r=mysql_query ("SELECTUsername from User WHERESignup_date>=curdate () ");//Query Cache works! $today=Date ("Y-M-d "); $r=mysql_query ("SELECTUsername from User WHERESignup_date>= '$today'");

2. Use explain to make your select query clearer

Using the Explain keyword is another MySQL optimization trick that lets you understand what the MySQL query is doing, which can help you find out where the bottleneck is, and shows 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 the Joins method), add your keyword explanation in it, here we can use phpMyAdmin, he will tell you the results in the table. For example, if I was forgetting to add a column to an index when I was executing joins, explain could help me find the problem.

After adding an index to group_id field:

3, using limit 1 to obtain the unique line

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

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

//Do I has anyUsers fromAlabama?//What not  toDo : $r=mysql_query ("SELECT *  from User WHEREState= 'Alabama'");if(Mysql_num_rows ($R)> 0) {// ... }//much better: $r=mysql_query ("SELECT 1  from User WHEREState= 'Alabama'LIMIT1");if(Mysql_num_rows ($R)> 0) {// ...}

4. Search fields in the index

An index is not just 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 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.

Additionally, the columns that are joined must be of the same type. For example, if you join a decimal column while adding an int column from another table, MySQL will not be able to use at least one of these metrics. Even if the character encoding must be the same as the string type.

//Looking forCompaniesinchmy state$r=mysql_query ("SELECTCompany_Name fromUsers Left JOINCompanies on(users.state=companies.state)WHEREUsers.id=$user_id");//both state columns should be indexed//  andThey both should be the same type and characterencoding// orMySQL might do Full TableScans

6. Do not use the by RAND () command

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

If you really need to show your results randomly, there are many better ways to achieve them. Admittedly, this will require more code, but it avoids the performance bottleneck. The problem is that MySQL may execute the by RAND () command for each individual row in the table (which consumes the processor's processing power), and then gives you just one row back.

//What not  toDo : $r=mysql_query ("SELECTUsername from User ORDER  by RAND() LIMIT1");//much better: $r=mysql_query ("SELECT Count(*) from User"); $d=mysql_fetch_row ($R); $Rand =Mt_rand (0, $d[0] - 1); $r=mysql_query ("SELECTUsername from UserLIMIT $Rand,1");

7. Try to avoid the SELECT * command

The more data is read from the table, the more slowly the query becomes. He increases the time it takes for the disk to operate, or if the database server is separate from the Web server. You will experience a very long network delay, simply because the data is not required to be transferred between servers.

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

//  notPreferred$r=mysql_query ("SELECT *  from User WHERE user_id = 1"); $d=Mysql_fetch_assoc ($R); echo "Welcome {$d[' username ']}";//Better: $r=mysql_query ("SELECTUsername from User WHERE user_id = 1"); $d=Mysql_fetch_assoc ($R); echo "Welcome {$d[' username ']}";//The differences is more significant withBigger result Sets

8. Get advice from procedure analyse ()

PROCEDURE analyse () gives you some advice on MySQL's column structure analysis and the actual data in the table. If the actual data already exists in your table, it can serve your major decision-making.

9. Prepared statements

Prepared statements can be useful both in terms of performance optimization and security.

The prepared statement, which filters the already bound variables by default, can protect the application effectively against SQL injection attacks. Of course you can also manually filter, but because most programmers forgetful character, it is difficult to achieve results.

// Createa prepared statementif($stmt=$mysqli -Prepare("SELECTUsername from User WHEREState=?")) {//bind parameters$stmt -Bind_param ("s", $state);// Execute$stmt -Execute();//bind result variables$stmt -Bind_result ($username);// Fetchvalue$stmt -Fetch();p rintf ("%S is  from %s\n ", $username, $state); $stmt -Close();}

10. Store the IP address as an unsigned integer

Many programmers do not realize that they can store IP addresses as integers when they create a varchar (15). 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 of type unsigned int, because the IP address will use the 32-bit unsigned integer.

= "UPDATESET= Inet_aton ('{$_server['remote_addr ') ]}'WHEREuser_id= $user_id';

Describes two functions:

Inet_aton () is an improved way to convert a string IP address into a 32-bit network sequence IP address.

Inet_ntoa () converts an IP into an Internet standard point-in-a-format string.

11. Do not add index to "gender"

Simply put, do not need, because gender, on the two value of male and female (shemale does not count, uh). It is not worthwhile to index these two values, because no matter how many records are created, the index of the gender is set up so that your statements are retrieved half as little. But with the index to create the loss ratio, picking sesame lost watermelon. (may not be accurate, but to the effect).

For example, the database is like a Xinhua dictionary, we check the data, can be based on pinyin to check, the word in the dictionary sort is based on pinyin to sort, we want to check a word, you can quickly find out according to pinyin we want to check the word, which is called as a clustered index! In other words, a clustered index is sorted by physics, and because it is physically ordered, a table can have only one clustered index and the fastest index. Of course, we can also be based on the radicals to check, but this kind of query must first find the radicals, and then to search the table to look at the word, finally to find the words we need, you can not look like Pinyin search the dictionary will be able to find, this is called Ordinary index. A normal index can have more than one.

If a dictionary is full of "male" and "female" two words, then in the search table also has a lot of "male" and "female", this to query help not very much.

MySQL Ten optimization tips