public static function performQueryAnalysis($sql, $version = null, $driver = null)
{
$hints = [];
if (preg_match('/^\\s*SELECT\\s*`?[a-zA-Z0-9]*`?\\.?\\*/i', $sql)) {
$hints[] = 'Use <code>SELECT *</code> only if you need all columns from table';
}
if (preg_match('/ORDER BY RAND()/i', $sql)) {
$hints[] = '<code>ORDER BY RAND()</code> is slow, try to avoid if you can.
You can <a href="http://stackoverflow.com/questions/2663710/how-does-mysqls-order-by-rand-work">read this</a>
or <a href="http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function">this</a>';
}
if (strpos($sql, '!=') !== false) {
$hints[] = 'The <code>!=</code> operator is not standard. Use the <code><></code> operator to test for inequality instead.';
}
if (stripos($sql, 'WHERE') === false) {
$hints[] = 'The <code>SELECT</code> statement has no <code>WHERE</code> clause and could examine many more rows than intended';
}
if (preg_match('/LIMIT\\s/i', $sql) && stripos($sql, 'ORDER BY') === false) {
$hints[] = '<code>LIMIT</code> without <code>ORDER BY</code> causes non-deterministic results, depending on the query execution plan';
}
if (preg_match('/LIKE\\s[\'"](%.*?)[\'"]/i', $sql, $matches)) {
$hints[] = 'An argument has a leading wildcard character: <code>' . $matches[1] . '</code>.
The predicate with this argument is not sargable and cannot use an index if one exists.';
}
if ($version < 5.5 && $driver === 'mysql') {
if (preg_match('/\\sIN\\s*\\(\\s*SELECT/i', $sql)) {
$hints[] = '<code>IN()</code> and <code>NOT IN()</code> subqueries are poorly optimized in that MySQL version : ' . $version . '. MySQL executes the subquery as a dependent subquery for each row in the outer query';
}
}
return $hints;
}