Optimising or fixing SQL queries: how to ask for help

Snails
As slow as a full table scan.

When an SQL query is slow or fails with an error, people occasionally ask for help in forums, mailing lists or social networks. Sometimes, if the problem is big enough, they purchase commercial support. Problem is, too often they don’t provide clear or sufficient information to help them.

This article is intended as a reference. When users on the web or customers don’t provide the information needed, I’ll point them here. Other people can do the same, of course. In which case, they can feel free to add their thoughts to the comments below, as usual.

Information needed to fix a query that fails with an error

Please provide the following information:

  • DBMS name and version.
  • The query text, with values.
  • The error number and message.
  • Table definitions.

DBMS name and version

Every technology is different, and all technologies evolve over time, so it’s important to provide the name of the DBMS in use (specifying if it’s a fork) and the version number. Here’s how to obtain this information, in details:

-- MySQL:
SHOW GLOBAL VARIABLES LIKE '%version%';
-- PostgreSQL:
pg_config --version
-- SQLite:
sqlite3 --version
-- Cassandra (with cqlsh):
SHOW VERSION;
-- Sphinx:
searchd

Query text

In a question, the following snippet is fine:

SELECT id, title FROM article WHERE id > '20' ORDER BY title LIMIT 10;

All values and column names must be there. If one of them is replaced with a ? you are not showing a query.

Error number and message

Posting a complete error number and message requires a trivial copy/paste.

The reason why the error number is necessary is that it serves as a reference. The error message is necessary because it may include additional information, like table names.

Table definitions

Some errors can only be found by looking at table definitions. Include the CREATE TABLE that can be used to recreate the table. That’s the best way to show a table definition: a single command includes column types and sizes, indexes, etc. Here’s how to obtain it:

-- MySQL:
SHOW CREATE TABLE table_name;
-- PostgreSQL (with psql):
\d table_name
-- PostgreSQL (with pg_dump):
pg_dump -st table_name database_name
-- SQLite (in SQL):
SELECT sql FROM sqlite_master WHERE name='table_name';
-- SQLite (sqlite3 client):
.schema table_name
-- Cassandra (cqlsh):
DESCRIBE TABLE table_name;
-- Sphinx:
DESCRIBE index_name;

Information needed to optimise a slow query

Please provide the following information:

  • DBMS name and version.
  • The query text, with values.
  • Table definitions.
  • Current query plan.
  • Optional: index distribution.

DBMS name and version´╗┐

See above.

Query text

See above.

Table definitions

See above.

Query plan

To find out why a query is slow, we should know how it is currently executed. Most technologies provide a decent way to show the current execution plan.

-- MySQL:
EXPLAIN EXTENDED SELECT ... ;
-- PostgreSQL:
EXPLAIN VERBOSE SELECT ... ;
-- SQLite:
EXPLAIN SELECT ... ;
-- Cassandra:
TRACING ON;
SELECT ... ;
TRACING OFF;
-- Sphinx:
SET profiling = 1;
SELECT ... ;
SHOW PLAN;
SET profiling = 0;

Index distribution

Some technologies provide a way to get information about index distribution. This information is useful to optimise some queries.

-- MySQL:
SHOW INDEX IN table_name;
-- PostgreSQL:
SELECT * FROM pg_class WHERE relname = 'table_name';

General advice

Be specific. For example, “this query doesn’t work” is the most common complain used in help request on Facebook. It could mean that the DBMS crashes, the query fails, the query is slow, you don’t get the results you expect, and so on. Therefore, it is not useful.

Don’t use terms you don’t understand. When I was a junior consultant, I spent my and customer’s time to investigate a deadlock problem, on a server that never experienced a deadlock. The reason is that the customer thought that lock is short for deadlock. Faulty communication wastes time and money, so be sure to avoid terminology you are not familiar with. You can be specific anyway.

Don’t filter as a texts. When a consultant asks you to run a command, use copy/paste. Change nothing. Then copy/paste the output. There is hardly a good reason to make changes to these strings, and it can result in a waste of time and money.

Ask about the problem, not your idea to solve it. If you explain the problem, a good consultant will usually find a solution. Don’t ask consultants help to apply a solution found on the web. Would you pay a doctor to perform some particular surgery on your stomach because your barman says you need it?

Toodle pip,
Federico

Photo source

Leave a Reply

Your email address will not be published. Required fields are marked *