Database skills MySQL top 10 optimization tips _ MySQL

WEB developers not only need to solve the program efficiency problem, but also need to quickly access and respond to databases. I hope this article will help you master the MySQL optimization skills. 1. optimize your MySQL Query cache for query on the MySQL server, and enable high-speed query cache MySQL optimization

WEB developers not only need to solve the program efficiency problem, but also need to quickly access and respond to databases. I hope this article will help you master the MySQL optimization skills.

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.

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.

11. // query cache works!

12. $ today = date ("Y-m-d ");

13. $ 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.

After adding the index to group_id field

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, adding a LIMIT 1 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.

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

2. // what NOT to do:

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

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

5 .//...

6 .}

7. // much better:

8. $ r = mysql_query ("SELECT 1 FROM user WHERE state = 'abama' LIMIT 1 ");

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

10 .//...

11 .}

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.