Abstract:

The content of most websites is stored in a database and accessed by users on request. Databases are very fast, and there are many tricks you can use to optimize the speed of your database so that you don’t waste resources on your server. In this article, I’ve included ten tips for optimizing database speed.

Design your database carefully

The first tip may seem obvious, but the truth is that most database problems come from poorly designed database structures.

For example, I have come across examples where client information and payment information are stored in the same database column. This is bad for systems and developers who use databases.

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

Source: www.simple-talk.com/sql/databas… If you want to optimize a query, it helps to know exactly what the result is. Use the EXPLAIN statement to get a lot of useful information. Here’s an example:

  　　Copy the code

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

Source: dev.mysql.com/doc/refman/…

3, the fastest query statement… The ones you didn’t send

Every time you send a statement to the database, you use up a lot of server resources. So in high-traffic sites, the best approach is to cache your queries.

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

AdoDB: AdoDB is a simplified database library for PHP. You can use it from a variety of database systems (MySQL, PostGreSQL, Interbase, etc.) and it’s designed for speed. AdoDB provides a simple but powerful caching system. Also, AdoDB has a BSD license that you can use for free in your projects. It also has a LGPL license for commercial projects.

Memcached:Memcached is a distributed memory caching system that reduces database load to speed up web sites 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

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 on a very small site, say, one user visits per minute, it may make no difference. However, with a high-traffic site 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 get data for a certain number of rows. Blogs, for example, only show 10 articles per page. In this case, 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 be iterating through all the rows, which is an unnecessary burden on the server.

SELECT title, excerpt, author FROM wp_posts LIMIT 10; 6. Avoid queries in the loop

When using SQL in PHP, you can place SQL in a loop. But doing so puts a burden on your database. The following example illustrates the problem of “nesting a query in a loop” :

  foreach ($display_order as $id= >$ordinal) {$sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
      mysql_query($sql); 
  } Copy the code

Here’s what you can do:

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)Copy the code

Source: www.karlrixon.co.uk/articles/sq…

7. Replace subqueries with joins

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

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

While subqueries are useful, they can be replaced by join statements, which execute faster. SELECT a.id, MAX(p.created) AS latest_post

FROM authors a INNER JOIN posts p ON (a.id = p.a thor_id) GROUP BY a.idCopy the code

Source: 20 bits.com/articles/10…

8. Be careful with wildcards

Wildcards are very useful for replacing one or more characters when searching for data. I’m not saying you shouldn’t, but you should be careful and don’t use full wildcards. Prefixed or postfixed wildcards can do the same thing.

In fact, a full-word wildcard search on millions of numbers can crash your database.

  　　
 Copy the code

# Full wildcard

SELECT * FROM TABLE WHERE COLUMN LIKE'%hello%';   Copy the code

# Postfix wildcard

SELECT * FROM TABLE WHERE COLUMN LIKE'hello%'; Copy the code

# Prefix wildcard

SELECT * FROM TABLE WHERE COLUMN LIKE'%hello'; 
Copy the code

Source: hungred.com/useful-info… 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 statements, where you can combine 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.q UNION SELECT * FROM a, b WHERE a.x = b.yCopy the code

Source: www.bcarter.com/optimsql.ht…

10. Use indexes

Database indexes are similar to those you see in libraries: they help you get to the information you want faster, just as library indexes help readers find the books they want faster.

Indexes can be created on a single 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);

Click to view the original article