Skip to content

Instantly share code, notes, and snippets.

@jonasraoni
Last active March 3, 2023 12:05
Show Gist options
  • Save jonasraoni/02350c841cfdb074e26986ea23105b7f to your computer and use it in GitHub Desktop.
Save jonasraoni/02350c841cfdb074e26986ea23105b7f to your computer and use it in GitHub Desktop.
Performs an optimization at a SELECT statement which is intended to be enclosed by a counter query (e.g. "SELECT COUNT(0) FROM (YOUR_GENERIC_QUERY)") for pagination purposes
<?php
print_r(optimizeCountQuery(
'SELECT (SELECT drop FROM expensive WHERE true = ?) AS subquery
FROM table
WHERE (SELECT ?) = param
ORDER BY not_needed_for_count, drop = ?',
[true,'param', true]
));
/*
Outputs the query without the SELECT fields and the ORDER BY clause, while also dropping the not needed parameters
Array
(
[0] => SELECT 0 FROM table WHERE (SELECT ?) = param
[1] => Array
(
[0] => param
)
)
*/
<?php
/**
* Retrieves a SELECT statement without the SELECT fields and ORDER BY clause for optimization purposes, as they might be expensive
* It supports generic parameters (?), pass your original parameters and the code will retrieve an updated copy (basically drop the not needed ones)
* @return array The SQL query at the index 0 and the updated parameters at the index 1
*/
function optimizeCountQuery(string $sql, array $params = []): array {
$findTopLevelExpression = static function (string $s, string $expression, int $index, ?int &$foundParams = null): int {
if ($index < 0)
return -1;
// Keeps the current level (0 means we are not inside a sub-query/function/etc)
$level = 0;
// Keeps the current opened delimiter
$delimiter = null;
for ($l = strlen($s), $i = $index; $i < $l; ) {
$found = match ($c = $s[$i]) {
// New context
'(' => ++$level,
// Close context
')' => $level-- || throw new Exception("Unexpected \")\" at {$i}"),
// Open/close delimiter
"'", '`', '"' => $delimiter === $c ? $delimiter = null : $delimiter ??= $c,
// Escape inside delimiter
$delimiter ? '\\' : null => ++$i,
// Param counter
'?' => ++$foundParams,
// Search for the expression, will return -1 if found
default => -(!$delimiter && !$level && preg_match("/\G{$expression}/i", $s, $m, 0, $i))
};
if ($found === -1)
return $i;
++$i;
}
$level && throw new Exception("Missing {$level} closing \")\"");
$delimiter && throw new Exception("Missing closing \"{$delimiter}\"");
return -1;
};
$paramsSkippedFromTop = 0;
// Abort if there's a "UNION" clause or if there's no "FROM"
if (~$findTopLevelExpression($sql, '\bUNION\b', 0) || !~($from = $findTopLevelExpression($sql, '\bFROM\b', 0, $paramsSkippedFromTop))) {
return [$sql, $params];
}
// Find the "ORDER BY" clause
$orderBy = $findTopLevelExpression($sql, '\bORDER\s+BY\b', $from);
$paramsSkippedFromBottom = 0;
// Look for the end of the query, just to retrieve the skipped params
$findTopLevelExpression($sql, '$', $orderBy, $paramsSkippedFromBottom);
$length = ~$orderBy ? $orderBy - $from : strlen($sql);
return [
// Slice the statement from the "FROM" clause up to the "ORDER BY" clause
'SELECT 0 ' . substr($sql, $from, $length),
// Slice the params which were dropped
array_slice($params, $paramsSkippedFromTop, $paramsSkippedFromBottom ? -$paramsSkippedFromBottom : count($params))
];
}
@jonasraoni
Copy link
Author

Enclose with a try/catch to retry the query using the original one (in case I did something bad or if you're a SQL master using custom database features) and profit 😂

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment