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

Question

When I run the following code, I get the error saying

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

$mysql = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database');
        if (mysqli_connect_errno()) {
            printf("DB error: %s", mysqli_connect_error());
            exit();
        }

    $get_emp_list = $mysql->prepare("SELECT id, name FROM calc");
    if(!$get_emp_list){
        echo "prepare failed\n";
        echo "error: ", $mysql->error, "\n";
        return;
    }
    $get_emp_list->execute();
    $get_emp_list->bind_result($id, $emp_list);

And this is the able schema --

--
-- Table structure for table `calc`
--

CREATE TABLE IF NOT EXISTS `calc` (
  `id` int(12) NOT NULL,
  `yr` year(4) NOT NULL,
  `mnth` varchar(12) NOT NULL,
  `name` varchar(256) NOT NULL,
  `paidleave` int(12) NOT NULL,
  `balanceleave` int(12) NOT NULL,
  `unpaidleave` int(12) NOT NULL,
  `basesalary` int(12) NOT NULL,
  `deductions` int(12) NOT NULL,
  `tds` int(12) NOT NULL,
  `pf` int(12) NOT NULL,
  `finalsalary` int(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Solution 1

Take a look at this bug-report : #35450 mysqli extension reports too many warnings

Quoting a few sentences of a note :

Mysqli extension throws too many warnings.
For example, "SELECT * FROM table" results in a warning: "Warning: mysqli::query(): No index used in query/prepared statement SELECT * FROM table ..."

And, quoting another note, which seems interesting :

Use mysqli_report() to disable that.

Solution 2

The fatal error is not in MySQL; the missing index notification is a relatively low-severity warning.

The fatal error is in your PHP code, because of the following three conditions:

• mysqli reports a lot of warnings, even for relatively harmless conditions.
• You're throwing mysqli_sql_exception for all errors and warnings due to your mysqli_report(MYSQLI_REPORT_ALL); line.
• Your PHP code is not catching that exception (i.e. it's not in a try{} block with an appropriate catch(){} block), and uncaught exceptions are fatal.

You can't do much about the first one, as mentioned in the other answer. So, you can fix it either by changing your mysqli_report(...) setting to MYSQLI_REPORT_STRICT or MYSQLI_REPORT_OFF, or indeed anything other than MYSQLI_REPORT_ALL.

(edit: w3d's comment below gives a good explanation why, and suggests you could use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) as a good alternative)

For best practices, and in combination with this, you should fix it properly by using try{} and catch(){} appropriately within your code.

Solution 3

mysqli_report(MYSQLI_REPORT_ALL ^ MYSQLI_REPORT_INDEX);

Turns off "Report if no index or bad index was used in a query" yet keeps other reporting on.

Comments

[Hrishikesh Choudhari]

Hmmm... I dont think thats deprecated, as I am using it very well.. mysqli_report(MYSQLI_REPORT_ALL);

[Pascal MARTIN]

Deprecated doesn't mean "doesn't work", but "shouldn't be used anymore, and might be removed one day or another"

[Hrishikesh Choudhari]

Right, but what I have is not a warning, it is a Fatal error. I would WANT to know about Fatal Errors.

[Jeremy Smyth]

It shouldn't have been deprecated (appears it was by accident): bugs.php.net/bug.php?id=55329

[mdicosimo]

It's often low-severity but should not be neglected: the message states that the mysql server has to perform a full table scan, i.e. has to check on every record wether it belongs into the result set or not. In case of big DB an additional index to the searched columns could help.

[MrWhite]

Specifically, to remove these particular warnings, you need to remove the MYSQLI_REPORT_INDEX flag from the report_mode property. This flag "Reports if no index or bad index was used in a query". So, use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) as opposed to mysqli_report(MYSQLI_REPORT_ALL)` before executing your query.

[Juha Untinen]

Yes, it is still a valid command in PHP7, with no deprecation message in the docs: php.net/manual/en/function.mysqli-report.php

[Juha Untinen]

This is not enough if you have a small table. In that case MySQL/MariaDB will implicitly disable indexes in the table, and combine that with mysqli throwing if no indexes are used, you have a nice when-stars-align mess :) "Works in prod, does not work in staging/dev".