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

This article mainly introduces "how to improve the speed of the database". In daily operation, I believe many people have doubts about how to improve the speed of the database. The editor consulted all kinds of information and sorted out simple and easy operation methods. I hope to help you answer the doubts about "how to improve the speed of the database"! Next, please follow the small series to learn together!

0. Carefully design databases

The first tip may seem obvious, but in fact most database problems stem from poorly designed database structures.

For example, I've come across instances where client information and payment information are stored in the same database column. For developers of systems and databases, this is bad.

When creating a new database, you should store the information in separate tables, using standard naming, and using primary keys.

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

1. Know what you need to optimize.

If you want to optimize a query statement, it's helpful to know exactly what the result of that statement will be. Using the EXPLAIN statement, you will get a lot of useful information. Here is 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

2. The fastest queries…are the ones you didn't send.

Every time you send a statement to the database, you consume a lot of server resources. So on high-traffic sites, the best way to do this is to cache your queries.

There are many ways to cache statements, but here are a few:

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

Memcached:Memcached is a distributed in-memory caching system that reduces database load and speeds up dynamic database-based websites.

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

3. Don't choose what you don't need.

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

SELECT * FROM wp_posts;

However, you should list only the columns you need, as shown below. If on a very small website, say, one user visits per minute, there may be no difference. However, if you have a website with a lot of traffic like Cats Who Code, it saves a lot of work for the database.

SELECT title, excerpt, author FROM wp_posts;

4. Use LIMIT

It is common to get data for only a certain number of rows. For example, a blog only displays ten 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, and you will be traversing all the rows, which is an unnecessary burden on the server.

SELECT title, excerpt, author FROM wp_posts LIMIT 10;

5. 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 within 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 categoriesSET display_order = CASE idWHEN 1 THEN 3WHEN 2 THEN 4WHEN 3 THEN 5ENDWHERE id IN (1,2,3)

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

6. Replace subqueries with join

As a programmer, subqueries are something that you can be tempted to use and abuse. Subqueries, as show below, can be very useful:

Programmers may like to use subqueries, or even abuse them. The following subqueries are useful:

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

While subqueries are useful, join statements can replace them, and join statements execute faster.

SELECT a.id, MAX(p.created) AS latest_postFROM authors aINNER JOIN posts pON (a.id = p.author_id)GROUP BY a.id

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

7. Be careful with wildcards

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

In fact, using full-word wildcards on millions of data sets can crash your database.

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

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

8. 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 together the results of two or more select statements. The following example returns the same result as above, but faster:

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

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

9. use the index

A database index is similar to an index you see in a library: it allows you to get the information you want faster, just as an index in a library allows readers to find the books they want faster.

An index can be created 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. This index is called idxModel.

CREATE INDEX idxModel ON Product (Model); At this point, the study of "how to improve the speed of the database" is over, hoping to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.