PHP throws mysqli_sql_exception 'No index used in query/prepared statement'
Fatal 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.
Fix it fast
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 .2Match what you see, then jump to its fix
- 1
The 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
You 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
- 1
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.phpConfirmed by php.net
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Back end
fetch_assoc() fails because the query returned false or true
mysqli_sql_exception
A fetch function needs a result set, but your query call handed it a boolean.
3 causes, each with a fixBack end
CodeIgniter 4: try/catch does not catch a failed insert in the model
DatabaseException
CodeIgniter throws a DatabaseException on a failed query only when DBDebug is true, and inside a transaction it does not throw unless you ask.
3 causes, each with a fixBack end
CodeIgniter 4 where() builds a different condition than you meant
A SQL syntax error that appears only when a value contains a quote character
The query builder joins every where() with AND and every orWhere() with OR, with no brackets.
3 causes, each with a fixBack end
Call to undefined function mysql_pconnect() after a PHP upgrade
Call to undefined function mysql_connect()
The old mysql_* functions belonged to the ext/mysql extension, which PHP removed in version 7.0.
2 causes, each with a fixBack end
CodeIgniter cannot connect to the MySQL database server
Unable to connect to your database server using the provided settings.
CodeIgniter only reports that its connection failed.
4 causes, each with a fixBack end
Laravel insert fails with 'Field user_id doesn't have a default value'
The insert works when the column is removed or made nullable
Error 1364 means Laravel sent an INSERT without a value for a NOT NULL column that has no default, and MySQL strict mode refuses it.
3 causes, each with a fix