10 tips for making your database faster

Most website content is stored in a 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. 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

3. The fastest query statements...are the ones you didn't send. Every time you send a statement to the database, you 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 database simplified library for PHP. 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. Using 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 queries within loops":

 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, it can be replaced by a join statement, which performs 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 when searching for data Wildcards can be used to replace one or more characters. 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 
      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 to replace 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 combine the results of 2 or more select statements together. The following example returns the same results as the 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 indexes

Database index and you are in the library The indexes seen in are similar: they allow you to get the information you want faster, just like the indexes in libraries allow readers to find the books they want faster.

　　可以在一个列上创建索引，也可以在多个列上创建。索引是一种数据结构，它将表中的一列或多列的值以特定的顺序组织起来。

　　下面的语句在Product表的Model列上创建索引。这个索引的名字叫作idxModel

   CREATE INDEX idxModel ON Product (Model);