Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

PHP throws mysqli_sql_exception 'No index used in query/prepared statement'

ErrorFatal error: Uncaught exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement'

Short answer

This is not a syntax error. mysqli was told to report queries that use no index (MYSQLI_REPORT_INDEX) and to throw exceptions (MYSQLI_REPORT_STRICT). Either turn the index report off by using the default mode, or add an index so the query stops scanning the whole table. Check with EXPLAIN which one applies.

  • Applies to PHP 8.3 and 8.4, mysqli, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find where the reporting mode is set. If it includes MYSQLI_REPORT_ALL or MYSQLI_REPORT_INDEX, the exception comes from that setting, not from the query.

    grep -rn "mysqli_report\|report_mode" --include=*.php .
  2. 2Match what you see, then jump to its fix

    1. 1The grep shows mysqli_report(MYSQLI_REPORT_ALL) or MYSQLI_REPORT_INDEX, and the query runs fine when the line is removedThe report mode includes MYSQLI_REPORT_INDEXFix
    2. 2You want the index report on, and EXPLAIN shows type ALL and a NULL key for the table in the WHERE clauseThe query really runs without an indexFix

What does the error look like?

  • Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement'
  • mysqli_sql_exception: No index used in query/prepared statement

Cause 1 of 2

The report mode includes MYSQLI_REPORT_INDEX

1Check

The PHP manual lists MYSQLI_REPORT_INDEX as 'report if no index or bad index was used in a query', and MYSQLI_REPORT_ALL as every option. MYSQLI_REPORT_STRICT turns reports into mysqli_sql_exception. Together they throw for any query that uses no index.

grep -rn "MYSQLI_REPORT_ALL\|MYSQLI_REPORT_INDEX" --include=*.php .

2Fix

Set the mode you actually want. From PHP 8.1 the default is MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, which throws on real errors and leaves the index report out. To keep every other report, subtract the index flag.

<?php
// PHP 8.1 and later default: errors throw, index report off
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// or: everything except the index report
mysqli_report(MYSQLI_REPORT_ALL & ~MYSQLI_REPORT_INDEX);

3Verify

Run the page that failed. The same query now returns its result.

php path/to/the-script-that-failed.php

Confirmed by php.net

Did this fix it?

Cause 2 of 2

The query really runs without an index

1Check

In MySQL's EXPLAIN output, type ALL means a full table scan, and a NULL key column means MySQL found no index to use. A NULL possible_keys column means there is no relevant index; look at the columns in the WHERE clause.

EXPLAIN SELECT id, email FROM users WHERE email = 'a@example.com';

2Fix

Create an index on the column or columns that the WHERE clause or the join uses. The statement is CREATE INDEX name ON table (columns).

CREATE INDEX idx_users_email ON users (email);

3Verify

Run EXPLAIN again. The key column now names the index, and the PHP exception no longer fires for that query.

EXPLAIN SELECT id, email FROM users WHERE email = 'a@example.com';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Set the mysqli report mode once, at the start of the application, and write down why if it includes the index flag.
  • Use MYSQLI_REPORT_INDEX only in development, where a warning about a missing index is useful, and not in production code paths.
  • Run EXPLAIN on every new query that filters or joins on a column before it ships.
  • Create indexes in a migration, not by hand on one server.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.