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;

Wastrel

Answer 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.

Sophistic

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

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

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

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

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

Answer 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.

Bellabelladonna

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. – Couching

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. – Lundgren

Answer 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.

Cromorne

Answer 4

Another way to fix it is to make your table column 'name' in MySQL an index.

ALTER TABLE `calc` ADD INDEX ( `name` ) ;

Willenewillet

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". – Marked

Answer 5

the message "No index used in query/prepared statement" not is about your code PHP it's about the design of your table, simply ADD a index to your table.

really the best is turn ON the error/warnings in DEVELOP:

mysqli_report(MYSQLI_REPORT_ALL);

but turn OFF in production:

mysqli_report(MYSQLI_REPORT_STRICT);

Prang