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.
The following is the list of possible warnings:
Aliasing a column wildcard, such as
SELECT tbl.* col1, col2probably 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.
LIMITclauses 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
OFFSETis 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 BYclauses that use cardinals instead of columns or expressions can cause problems if the query or table is changed.
Constant expressions in
ORDER BYclauses are at best useless operations that do not change query results. (MySQL only)`
ORDER BYclauses 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)
ORDER BYclauses that sort the results in different directions prevent indexes from being used. All expressions in the
ORDER BYclause must be ordered either
DESCso 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 BYquery can cause non-deterministic results. (MySQL only)
ORDER BYcauses non-deterministic results, depending on the query execution plan. (MySQL only)