Optimizing Queries

Execution Plan

A query execution plan is a series of steps used to access data in RDBMSes when returning statements

  • An RDBMS will often provide several methods for returning the details of a query execution plan.
  • Some RDBMSes offer tools which create a graphical representation of their query plans, while others allow a distinct mode to be set on the connection which causes the RDBMS to return a textbased description of their query plans.
  • RDBMSes allow you to query a virtual database table, after running the query, by using an EXPLAIN statement.

Query Optimization

  • Most RDBMSes have a query optimization feature that uses a query optimizer tool to calculate the most efficient method for executing a query by evaluating all the available query execution plans.
  • When a query gets submitted to the database, the query optimizer evaluates the various possible query execution plans and returns what it determines to be the best choice.
  • Query optimizers can be fallible, so database admins will sometimes need to manually inspect and fine-tune the plans produced by the query optimizer to get optimum query performance.
  • Some RDBMSes allow you to provide hints to the query optimizer. A hint is an additional component to the SQL statement that informs the database engine about how it wants it to execute a query, such as instructing the database engine to use an index when executing the query, even though the query optimizer might have decided not to

EXPLAIN Tools

  • All flavors of RDBMSes, such as MySQL, PostgreSQL, and Db2 have an EXPLAIN statement that you can use to show a text-based representation of the details of a query execution plan for a statement, including the processes that occur and in what order they occur.
  • An EXPLAIN statement can be a good way to swiftly cure slow running queries.
  • Some RDBMSes also provide a graphical version of the EXPLAIN statement.
  • For example, Db 2’ Visual Explain uses information from a number of sources to enable you to view the access plan for explained SQL or XQuery statements as a graph. You can use the information available from the access plan graph to tune your queries for better performance.
  • For MySQL systems, the MySQL Workbench provides a Visual Explain Plan which produces and presents a visual representation of the MySQL EXPLAIN statement. MySQL Workbench provides all of the
    • EXPLAIN formats for executed queries including the standard format
    • the raw extended JSON format, and
    • the visual query plan
  • PostgreSQL systems the PgAdmin utility provides a graphical explain plan feature. Although this is not an entire substitute for EXPLAIN or EXPLAIN ANALYZE text plans, it does offer a fast and simple method for viewing plans for additional analysis.
  • There also many third-party tools that can provide these same capabilities.

Recap

  • Database performance is measured by using key performance indicators, known as metrics, that enable DBAs to optimize organizations’ databases.
  • You should monitor at the infrastructure, platform, query, and user levels.
  • A database diagnostic log file, also known as an error log or troubleshooting log, contains timestamped messages for various types of informational messages, events, warnings, and error details.
  • Database optimization commands include OPTIMIZE TABLE in MySQL, VACUUM, and REINDEX in PostgreSQL, and RUNSTATS and REORG in Db2.
  • Query execution plans show details of the steps used to access data when running query statements.
  • Performance monitoring, dashboards and reports, and server/database logs help identify bottlenecks.
  • Database automation is the use of unattended processes and self-updating procedures for basic database tasks.
  • Some automation processes include backing up, truncating, and restoring databases.
  • Reports give a regular overview of database health, notifications give a forewarning of a situation that could become troublesome if not addressed, and alerts bring awareness to an issue that needs immediate attention.