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

example.php

<?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
        )

)
*/

optimizeCountQuery.php

<?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))
	];
}

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 😂