mysqli_sql_exception - 'No index used in query/prepared

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
laiseng
Forum Newbie
Posts: 2
Joined: Sat Jun 10, 2006 7:43 pm

mysqli_sql_exception - 'No index used in query/prepared

Post by laiseng »

i have this error
Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement SELECT * FROM members' in /var/www/html/myfyp/db/members.class.php:142 Stack trace: #0 /var/www/html/myfyp/db/members.class.php(142): mysqli->query('SELECT * FROM m...') #1 /var/www/html/myfyp/login.php(13): members->login('admin', 'admin1') #2 {main} thrown in /var/www/html/myfyp/db/members.class.php on line 142

how do i fix this?

what does it mean by no index?
derchris
Forum Commoner
Posts: 44
Joined: Sat Jun 10, 2006 6:14 pm

Post by derchris »

Looks like an error with your DB.
How does your table structure looks like ?
laiseng
Forum Newbie
Posts: 2
Joined: Sat Jun 10, 2006 7:43 pm

Post by laiseng »

it look like this

Code: Select all

CREATE TABLE `members` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `USERNAME` varchar(200) NOT NULL,
  `PASSWORD` varchar(200) NOT NULL,
  `FIRSTNAME` varchar(200) NOT NULL,
  `LASTNAME` varchar(200) NOT NULL,
  `DOB` date NOT NULL,
  `COUNTRY` varchar(200) NOT NULL,
  `STATE` varchar(200) default NULL,
  `ZIP` int(5) NOT NULL,
  `BLOGURL` varchar(500) NOT NULL,
  `EMAIL` varchar(200) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

i've able to by pass this fatal error by doing mysqli_report(MYSQLI_REPORT_OFF)

does this mean that my db and code are alright after turning off the report and beable to excute the script without error?
derchris
Forum Commoner
Posts: 44
Joined: Sat Jun 10, 2006 6:14 pm

Post by derchris »

I think it is because you set

PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)

which is somehow possible but should lead to errors, like in your case.
torpedo83
Forum Newbie
Posts: 3
Joined: Tue Oct 03, 2006 1:54 pm
Location: Barletta (BA), Italy

Post by torpedo83 »

I've the same problem 'No index used in query/prepared statement' even if i've a simple table without PK or UNIQUE columns.... so i don't know what causes the problem...

The code i used is below:

Code: Select all

$conn = new mysqli ($IP, $USERNAME,$PASSWORD,$DB_NAME);
if (mysqli_connect_errno()) die ("Error while connecting!");

try 
{
	$result = $conn->query("SELECT * FROM articoli LIMIT 10");
}
catch (mysqli_sql_exception $e)
{
	echo $e->getMessage();
}
torpedo83
Forum Newbie
Posts: 3
Joined: Tue Oct 03, 2006 1:54 pm
Location: Barletta (BA), Italy

Post by torpedo83 »

:D :D :D Ok i've fixed the problem ... what i did was just TURNING OFF the mysqli error reporting system just using this instruction:

Code: Select all

mysqli_report (MYSQLI_REPORT_OFF);
So be careful when you decide to use it, just because it throws FATAL and not WARNING or NOTICE error that is to say script blocking!!!!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

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. That's not what a database is supposed to do in the first place.
Post Reply