Guidelines for SQL

These guidelines provide some tips for writing efficient SQL code. Guidelines apply to MySQL and PostgreSQL.

Install tools to measure performance of SQL queries

In this guide, the following set-up is needed:

Logging slow SQL queries server-side in PostgreSQL

A first solution is to modify PostgreSQL postgresql.conf configuration file:

The Benefits of Indexing Large MySQL Tables

The use of indexes to assist with large blocks of tables, data may have considerable impact on reducing MySQL query execution and, thus,

Run EXPLAIN on slow queries to find sequential scans

A database system executes each query in one or several steps.

Using MySQL EXPLAIN

To display the query plan, run EXPLAIN my_query using an SQL client like PhpMyAdmin or any other MySQL client.

Using PostgreSQL EXPLAIN

To display the query plan, run EXPLAIN my_query using an SQL client like pgAdmin3 or any other PostgreSQL client.

Understanding the logic behind EXPLAIN ANALYSE

When processing queries, the database has two possibilities:

Indexes are not symmetric

Multi-column indexes can be rather tricky to understand. An index on several columns is not symmetric and doesn't work equally on every

Make sure that JOIN columns both carry indexes of the same type

Running a JOIN on two columns of different data types may force a sequential scan.

Conclusion and summary of the guidelines

Writing code compliant with both MySQL and PostgreSQL

Writing code compliant with both MySQL and PostgreSQL