10 Proven Techniques to Supercharge Your Database Performance

Most website content resides in databases, and inefficient queries can waste server resources. This article compiles ten practical techniques for speeding up database operations.

1. Design Your Database Carefully

A well‑structured schema prevents many performance problems. Separate distinct data (e.g., client and payment information) into different tables, use clear naming conventions, and define primary keys.

2. Identify What to Optimize

Use the EXPLAIN statement to see how MySQL executes a query and pinpoint bottlenecks.

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

3. Cache Queries Whenever Possible

Repeated queries consume resources; caching them reduces load. Options include:

AdoDB – a PHP database abstraction layer with built‑in caching (BSD/LGPL licensed).

Memcached – a distributed memory cache that offloads reads from the database.

CSQL Cache – an open‑source caching framework.

4. Select Only Needed Columns

Avoid SELECT *. Specify the exact columns required, which reduces data transferred and processing time.

SELECT title, excerpt, author FROM wp_posts;

5. Use LIMIT to Restrict Result Sets

When only a subset of rows is needed (e.g., pagination), apply LIMIT to avoid scanning the entire table.

SELECT title, excerpt, author FROM wp_posts LIMIT 10;

6. Avoid Queries Inside Loops

Running a query for each iteration adds unnecessary overhead. Instead, batch updates into a single statement.

Loop version (inefficient):

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

Batch version (efficient):

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);

7. Replace Subqueries with Joins

Joins are generally faster than correlated subqueries.

Subquery example:

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

Join equivalent:

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;

8. Use Wildcards Wisely

Full‑wildcard searches ( LIKE '%term%') on large tables can cripple performance. Prefer prefix or suffix wildcards.

# Full wildcard (slow)
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';
# Prefix wildcard (better)
SELECT * FROM TABLE WHERE COLUMN LIKE 'hello%';
# Suffix wildcard (better)
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello';

9. Replace OR with UNION

Splitting an OR condition into separate SELECT statements combined with UNION can improve execution speed.

SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x = b.y;

10. Create Proper Indexes

Indexes work like a book's index, allowing rapid lookup of rows. They can be single‑column or multi‑column.

CREATE INDEX idxModel ON Product (Model);

Source: http://geek.csdn.net/news/detail/249705