Query Analysis Rules

For MySQL and PostgreSQL, each query sample which VividCortex captures is analyzed to see whether it fails one (or more) of a number of rules, each of which is designed to detect possible problems with the query. When a query fails a rule, those failures are displayed when viewing a sample of that query underneath the Connection ID next to Failed Rules.

Query Samples

The following is the list of possible warnings:

  • Aliasing a column wildcard, such as SELECT tbl.* col1, col2 probably indicates a bug in your SQL. You probably meant for the query to retrieve col1, but instead it renames the last column in the *-wildcarded list.

  • The table or column’s alias is the same as its real name, which makes the query harder to read.

  • A SELECT statement without WHERE/GROUP/LIMIT clauses could examine many more rows than intended.

  • ORDER BY RAND() or ORDER BY Random() is a very inefficient way of retrieving a random row from the results because it sorts the entire result and then throws most of it away.

  • Paginating a result set with LIMIT and OFFSET is O(n^2) complexity, and will cause performance problems as the data grows larger. Pagination techniques such as bookmarked scans are much more efficient. (MySQL only)

  • GROUP BY clauses that use cardinals instead of columns or expressions can cause problems if the query or table is changed.

  • Constant expressions in GROUP BY or ORDER BY clauses are at best useless operations that do not change query results. (MySQL only)`

  • GROUP BY or ORDER BY clauses with columns from different tables will force the use of a temporary table and filesort, which can be a huge performance problem and can consume large amounts of memory and temporary space on disk. (MySQL only)

  • GROUP BY or ORDER BY clauses that sort the results in different directions prevent indexes from being used. All expressions in the ORDER BY clause must be ordered either ASC or DESC so that MySQL can use an index. (MySQL only)

  • Matching an argument with a leading wildcard, such as “%foo”, prevents the database from using an index to identify matching rows. Avoid leading wildcards where possible. (PostgreSQL only)

  • Selecting all columns with the * wildcard will cause the query’s meaning and behavior to change if the table’s schema changes, and might cause the query to retrieve too much data.

  • Evidence of SQL injection. (If this rule fails, it will also automatically trigger a critical Event.)

  • Selecting non-grouped columns from a GROUP BY query can cause non-deterministic results. (MySQL only)

  • LIMIT without ORDER BY causes non-deterministic results, depending on the query execution plan. (MySQL only)