Guidelines for SQL

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

• MySQLism and PostgreSQLism: DELETE on JOINS requires the USING syntax
• MySQLism and PostgreSQLism: avoid deleting data in several tables in one query
• MySQLism: MySQL considers INT and BOOLEAN the same
• MySQLism: MySQL will automatically cast INT into VARCHAR/CHAR
• MySQLism: Use CASE instead of IF().
• MySQLism: Use SELECT(DISTINCT ) only for one field, use SELECT COUNT(*) FROM (SELECT DISTINCT ... ) ... for multiple
• MySQLism: avoid UPDATE on duplicate
• MySQLism: avoid ambiguous queries
• MySQLism: avoid nested ORDER BY, use nested queries
• MySQLism: avoid using IF() in SELECT use CASE()
• MySQLism: avoid using MySQL IGNORE syntax
• MySQLism: avoid using MySQL REPLACE syntax
• MySQLism: avoid using double quotes for alphanumerical values
• MySQLism: use TEXT values and not VARCHAR(255)
• Oracleism: Don't use AS for table aliases
• PostgreSQLism: CONCAT() can only take two arguments (D6 only, fixed in D7)
• PostgreSQLism: all non-aggregated fields to be present in the GROUP BY clause
• PostgreSQLism: avoid mutiple ROW inserts (PostgreSQL < 8.2 only)
• Testing SQL 99 compliance
• Writing code compliant with both MySQL and PostgreSQL