ANALYZE

MySQL

Example

ANALYZE TABLE employees;

Output

+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| company.employees | analyze | status   | OK       |
+----------------+---------+----------+----------+

Explanation

The ANALYZE TABLE statement collects and stores the distribution of key values for a table. It can be used to improve join operations of columns referenced constantly in queries. In this example, we analyzed the table employees and the output shows the operation was successful.

PostgreSQL

Example

CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    text VARCHAR(100)
);

INSERT INTO test (text)
VALUES ('PostgreSQL'), ('is'), ('great');

ANALYZE test;

Output

ANALYZE

Explanation

The ANALYZE command collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. This information is subsequently used by the query planner to help determine the most efficient execution plans for queries.

Oracle

Example

ANALYZE TABLE employees COMPUTE STATISTICS;

Output

Table analyzed.

Explanation

The ANALYZE statement is used to collect statistics on the target table so that the Oracle server can choose the fastest way to handle queries. In this example, Oracle collects statistics for the table named “employees”.

SQLite

Example

ANALYZE;

Output

No output is displayed when the ANALYZE command is run.

Explanation

The ANALYZE command in SQLite does not produce any immediate output. What it does is that it gathers statistics about the tables and indices in the database and stores them in a system table named sqlite_stat1. These statistics are subsequently being used by the query planner to help it determine the most efficient algorithm for executing a SQL statement.