10 suggestions to get the database up and running. __ Database

1, 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.

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

2, clear you need to optimize the place

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:

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 quickest query statement ... It's 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 very high traffic site, the best way is to cache your query statements.

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

Adodb:adodb is a database simplification library for PHP. Using it, you can choose a different database system (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 that can reduce the load on the database to speed up Web sites based on dynamic databases.

The Csql cache:csql cache is an open source data caching architecture. I haven't tried it, but it looks great.

4, do not select what you do not 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'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.

SELECT title, excerpt, author from Wp_posts;

5, the use of 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.

SELECT title, excerpt, author from Wp_posts LIMIT 10;

6, avoid the query in the loop

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":

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 then
        2 THEN 4 when
        3 THEN 5    
 

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 misuse. The following subquery is useful:

Select a.ID
       (select MAX (created) from
         posts
        WHERE author_id = a.id) 
  

Although a subquery is useful, a join statement can replace it, and 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 )　　
  

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

8. Use wildcard characters with care

Wildcards are very handy, and you can use wildcards to replace one or more characters when searching for data. I'm not saying no, but it should be used with care and do not use the whole word wildcard (full wildcard), the prefix wildcard or the back wildcard to accomplish the same task.

In fact, using the whole word wildcard on millions of levels of data will make your database a machine.

#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 is like　  

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

9, use Union to replace or

The following example uses an OR statement to:

SELECT * from A, b WHERE a.p = B.Q or a.x = B.Y;

Union statement, you can spell 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 
   

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 the reader to find the book as quickly as possible.

You can create indexes on one column, or on multiple columns. An index is a data structure in which the values of one or more columns in a table are organized in a specific order.

The following statement creates an index on the model column of the product table. The name of this index is called Idxmodel

CREATE INDEX Idxmodel on Product (Model);