Shulou(Shulou.com)05/31 Report--

What are the suggestions to make the database change quickly? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The content of most websites is stored in the database, and users access the content through requests. The database is very fast, and there are many techniques that allow you to optimize the speed of the database so that you don't waste the resources of the server. In this article, I have included ten tips for optimizing database speed.

1. Carefully design the database

* techniques may seem like a matter of course, but in fact most of the database problems come from poorly designed database structures.

For example, I have come across an example of storing client information and payment information in the same database column. This is bad for developers of systems and databases.

When you create a new database, you should store the information in different tables, use standard naming methods, and use primary keys.

Source: http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

2. Be clear about what you need to optimize

If you want to optimize a query statement, it is helpful to know the results of this statement clearly. Using the EXPLAIN statement, you will get a lot of useful information. Let's look at an example:

EXPLAIN SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column

Source: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

3. The fastest query statement. It's the words you didn't send.

Every time you send a statement to the database, you use a lot of server resources. So in high-traffic websites, the way to do this is to cache your queries.

There are many ways to cache statements, several of which are listed below:

AdoDB: AdoDB is a database simplification library for PHP. With it, you can choose different database systems (MySQL, PostGreSQL, Interbase, etc.), and it is designed for speed. AdoDB provides a simple but powerful caching system. Also, AdoDB has a BSD license, and you can use it for free in your project. For commercial projects, it also has a LGPL license.

Memcached:Memcached is a distributed memory caching system, which can reduce the load of the database and speed up the website based on the dynamic database.

CSQL Cache: CSQL caching is an open source data caching architecture. I haven't tried it, but it looks great.

4. Don't select what you don't need

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

SELECT * FROM wp_posts

However, you should only list the columns you need, as shown below. If you visit a very small website, for example, one user a minute, it may make no difference. However, if you have a site with a lot of traffic like Cats Who Code, it saves a lot of trouble for the database.

SELECT title, excerpt, author FROM wp_posts

5. Adopt LIMIT

It is very common to get data for only a specific number of rows. For example, a blog displays only ten articles per page. At this point, you should use LIMIT to limit the number of rows of data you want to select.

If there is no LIMIT and the table has 100000 rows of data, you will traverse all the rows, which is an unnecessary burden on the server.

SELECT title, excerpt, author FROM wp_posts LIMIT 10

6. Avoid queries in loops

When using SQL in PHP, you can put SQL in a loop statement. But doing so puts a burden on your database.

The following example illustrates the problem of "nesting query statements in loop statements":

Foreach ($display_order as $id = > $ordinal) {$sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id"; mysql_query ($sql);}

You can do this:

UPDATE categories SET display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END WHERE id IN

Source: http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/

7. Use join to replace the subquery

Programmers may like to use subqueries or even abuse them. The following subquery is very useful:

SELECT a.id, (SELECT MAX (created) FROM posts WHERE author_id = a.id) AS latest_post FROM authors a

While a subquery is useful, the join statement can replace it, and the join statement executes faster.

SELECT a.id, MAX (p.created) AS latest_post FROM authors an INNER JOIN posts p ON (a.id = p.author_id) GROUP BY a.id

Source: http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/

8. Be careful with wildcards

Wildcards are very useful, and you can use wildcards instead of one or more characters when searching for data. I'm not saying you can't use it, but you should use it carefully and don't use full-word wildcards (full wildcard). Prefix wildcards or post-wildcards can accomplish the same task.

In fact, searching with full-word wildcards on millions of orders of magnitude of data can bring your database down.

# Full wildcard SELECT * FROM TABLE WHERE COLUMN LIKE'% hello%'; # Postfix wildcard SELECT * FROM TABLE WHERE COLUMN LIKE 'hello%'; # Prefix wildcard SELECT * FROM TABLE WHERE COLUMN LIKE'% hello'

Source: http://hungred.com/useful-information/ways-optimize-sql-queries/

9. Use UNION instead of OR

The following example uses the OR statement:

SELECT * FROM a, b WHERE a.p = b.Q or a.x = b.y

UNION statement, you can put the results of 2 or more select statements together. The following example returns the same result as above, but faster:

SELECT * FROM a, b WHERE a.p = b.Q UNION SELECT * FROM a, b WHERE a.x = b.y

Source: http://www.bcarter.com/optimsql.htm

10. Use index

The database index is similar to the index you see in the library: it allows you to get the information you want more quickly, just as the index in the library allows readers to find the books they want more quickly.

You can create an index on one column or on multiple columns. An index is a data structure that organizes the values of one or more columns in a table in a specific order.

The following statement creates an index on the Model column of the Product table. The name of this index is idxModel

CREATE INDEX idxModel ON Product (Model): after reading the above, have you mastered the ways to make the database change faster? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.