10 Tips to Optimize Database Speed

10 Tips to Optimize Database Speed

Most website content 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 do not waste server resources. In this article, I’ve included ten tips for optimizing database speed.

　1. Design the database carefully

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

　For example, I once encountered an example where client information and payment information were stored in the same database column. This is bad for system and database developers.

When creating a new database, information should be stored in different tables, using standard naming methods, and using primary keys.

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

2. Know what you need to optimize

If you want to optimize something Query statement, it is very helpful to clearly know the result of this statement. 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 statements...are the ones you didn’t send

　Every time you send a statement to the database, you will use a lot of server resources . So in a website with high traffic, the best way is to cache your query statement.

　There are many ways to cache statements, a few are listed below:

　AdoDB: AdoDB is a PHP database simplified library. Using it, you can choose from 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, so you can use it in your projects for free. For commercial projects, it also has an LGPL license.

　 Memcached: Memcached is a distributed memory caching system that can reduce the load on the database and accelerate websites based on dynamic databases.

　 CSQL Cache: CSQL Cache 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

　To get the data you want, a very common way is to use the * character, which will list all columns.

SELECT * FROM wp_posts;

However, you should only list the columns you need as shown below. If on a very small website, say, one user visits a minute, it may not make any difference. However, if it is a high-traffic website like Cats Who Code, this saves a lot of work for the database.

SELECT title, excerpt, author FROM wp_posts;

　5. Use LIMIT

It is very common to only obtain data for a specific number of rows. For example, a blog only displays ten articles per page. At this time, 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 100,000 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 an additional 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 (1,2,3 )

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

7. Use join To replace subqueries

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

Although the subquery is useful, the join statement can replace it, The join statement executes faster.

SELECT a.id, MAX(p.created) AS latest_post FROM authors a 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. Use wildcards carefully

　 Wildcards are very easy to use. You can use wildcards to replace one or more characters when searching for data. I'm not saying you can't use it, but you should use it with caution and don't use full wildcards. Prefix wildcards or postfix wildcards can accomplish the same task.

　In fact, using whole-word wildcard searches on millions of data will crash your database.

  #Full wildcard             SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';         #Postfix wildcard                        . %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 results as the above, but is faster: SELECT * FROM a, b WHERE a.p = b.q SELECT * FROM a, b WHERE a.x = b.y SELECT * FROM a, b WHERE a.x = b.y Source: http://www .bcarter.com/optimsql.htm

　10. Use indexes

　 Database indexes are similar to the indexes you see in libraries: they allow you to obtain the information you want faster, just as indexes in libraries can Readers can 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. The name of this index is idxModel

CREATE INDEX idxModel ON Product (Model);

The above are 10 tips to optimize database speed. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!