10 recommendations for optimizing a database

Carefully design the database

The first technique may seem natural, but in fact most of the database's problems come from poorly designed database structures. For example, I have ever met a sample of storing client information and payment information in the same database column. This is bad for both the system and the developers using the database. When creating a new database, you should store the information in a different table, adopt a standard naming method, and use a primary key.

Figure out where you need to optimize.

If you want to optimize a query, it is helpful to know the result of the statement clearly. Using the explain statement, you'll get a lot of useful information, and here's an example:

[SQL] view plain copy print? EXPLAIN SELECT * from ref_table,other_table WHERE ref_table.key_column=other_table.column;

To cache data

Every time you send a statement to the database, you will use a lot of server resources. So in a very high traffic site, the best way is to cache your query statements. Memcached:memcached is a distributed memory caching system that can reduce the load on the database to speed up Web sites based on dynamic databases.

Do not query for fields that you do not want

A very common way to get the data you want is to use the * character, which lists all the columns.

[SQL] view plain copy print? SELECT * from Wp_posts;
However, you should only list the columns you need, as shown below. If you're on a very small site, for example, one minute for a user to visit, there may be no difference. However, if a site as large as cats who code is, this will save a lot of things for the database.

[SQL] view plain copy print? Elect title, excerpt, author from Wp_posts;

Using limit

It is very common to get data for only a specific number of rows. Blogs, for example, display only 10 articles per page. At this point, you should use limit to limit the number of rows you want to select.

Without limit, the table has 100,000 rows of data, and you will iterate through all the rows, which is an unnecessary burden for the server. [SQL] view plain copy print? SELECT title, excerpt, author from Wp_posts LIMIT 10;
Avoid loops in the query

When you use SQL in PHP, you can put SQL in a loop statement. But doing so adds a burden to your database.

The following example illustrates the problem of "nesting query statements in circular statements":[PHP]View Plain copy print? foreach ($display _order as $id => $ordinal) {$sql = "UPDATE categories SET Display_order = $ordinal WHERE id =        $id ";   mysql_query ($sql); }
[PHP]View Plain copy print? UPDATE categories SET Display_order = case ID when 1 THEN 3 then 2 THEN 4 when 3 T HEN 5 end WHERE ID in (1, 2, 3) replaces a subquery with a join

Programmers may like to use subqueries or even misuse. The following subquery is useful:

[SQL] view plain copy print? Select a.ID (select MAX (created) from posts WHERE author_id = a.id) as Latest_post from authors a
Although a subquery is useful, a join statement can replace it, and the join statement executes faster.

[SQL] view plain copy print? SELECT a.id, MAX (p.created) as Latest_post