Static analysis of database access code is a real thing since phpstan-dba. Just stick to the rules and you will be covered:

When enabled, phpstan-dba will report errors in case queries are inefficient. The analyzer is reporting problems related to queries not using index, full-table-scans and too many unindexed reads.

use Doctrine\DBAL\Connection;

class Foo
{
    public function unindexRead(Connection $conn, string $email): void
    {
        $conn->executeQuery('SELECT * FROM ada WHERE email = ?', [$email]);
    }
}

phpstan-dba will report

Query is not using an index on table ‘ada’.

Consider optimizing the query. In some cases this is not a problem and this error should be ignored.

Within your phpstan-dba-bootstrap.php file, you need to optionally enable query plan analysis:

Configuration

Signature

analyzeQueryPlans($numberOfAllowedUnindexedReads = true, $numberOfRowsNotRequiringIndex = QueryPlanAnalyzer::TABLES_WITHOUT_DATA)

Examples

Passing true will enable the feature:

$config = new RuntimeConfiguration();
$config->analyzeQueryPlans(true);

For more fine grained control, you can pass a positive-integer describing the number of unindexed reads a query is allowed to execute before being considered inefficient. This will only affect queries which already use an index.

$config = new RuntimeConfiguration();
$config->analyzeQueryPlans(100000);

To disable the efficiency analysis but just check for queries not using indices at all, pass 0:

$config = new RuntimeConfiguration();
$config->analyzeQueryPlans(0);

When running in environments in which only the database schema, but no data is available pass QueryPlanAnalyzer::TABLES_WITHOUT_DATA:

$config = new RuntimeConfiguration();
$config->analyzeQueryPlans(true, QueryPlanAnalyzer::TABLES_WITHOUT_DATA);

In case you are running a real database with production quality data, you should ignore tables with only few rows, to reduce false positives:

$config = new RuntimeConfiguration();
$config->analyzeQueryPlans(true, QueryPlanAnalyzer::DEFAULT_SMALL_TABLE_THRESHOLD);

Note: For a meaningful performance analysis it is vital to utilize a database, which contains data and schema as similar as possible to the production database.

Note: “Query Plan Analysis” requires an active database connection.

Note: “Query Plan Analysis” is not yet supported on the PGSQL driver

Read more

Please find more articles related to phpstan-dba and its featureset.

Found a bug? Please help improve this article.

<
Previous Post
Phpstan Non Falsy String
>
Next Post
2022 Wrap Up