mysqli fatal error: No index used in query/prepared statement

Question

I want to execute a simple prepared Statement using mysqli, but it won't work.

I have this table:

CREATE TABLE IF NOT EXISTS `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  `password` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=4 ;

And want to print the id of a specific email.

$mysqli = new mysqli($server,$user,$pass,$db);

if(mysqli_connect_errno()) {
    echo "Connection Failed: " . mysqli_connect_errno();
    exit();
 }
 $user = "test@dada.com";
 $pass = "dada";
/* Create a prepared statement */
if($stmt = $mysqli -> prepare("SELECT * FROM account WHERE email=?
AND password=?")) {

  /* Bind parameters
     s - string, b - blob, i - int, etc */
  $stmt -> bind_param("ss", $user, $pass);

  /* Execute it */
  $stmt -> execute();

  /* Bind results */
  $stmt -> bind_result($result);

  /* Fetch the value */
  $stmt -> fetch();

  echo $user . "id of user is " . $result;

  /* Close statement */
  $stmt -> close();
}

/* Close connection */
$mysqli -> close();

But i get following Error:

Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement SELECT * FROM account WHERE email=? AND password=?' mysqli_stmt->execute() #1 {main}

Answer 1

Your problem is that the query you are executing is going to be inefficient without using an INDEX.

SELECT * FROM account WHERE email=? AND password=?

There's no index on any of the two fields you have used in the WHERE clause. One solution would be to create an index on both fields which should make the error go away.

ALTER TABLE account ADD INDEX `index_on_email_and_password` (email, password);

In many situations, you know that the INDEX is not going to improve the performance, so you can safely ignore this error. To do so, replace the following line of code:

mysqli_report(MYSQLI_REPORT_ALL);

with one of these

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// or 
mysqli_report(MYSQLI_REPORT_ALL & ~MYSQLI_REPORT_INDEX);

This will keep on reporting normal SQL errors, but it will ignore all warnings about bad indexes.

Answer 2

Well I think you have to do this:

CREATE TABLE IF NOT EXISTS `account` (
  `id` PRIMARY KEY int(11) NOT NULL AUTO_INCREMENT,
   // the rest

The above code makes the id field of your table as PRIMARY KEY so it never repeats itself and it remains the index of your table.