MySQL Set Increment Field plus 1

[eluser]RaZoR LeGaCy[/eluser]
Hello,

I was looking at my MySQL Slow Log and noticed this update took 29 seconds.

# Query_time: 29 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use hh_;
UPDATE `views` SET `hits` = hits+1 WHERE `rid` = '62';

I then ran the query through PHPMYADMIN using explain and received the following message:
Error

SQL query:

EXPLAIN UPDATE `hh_skViews` SET `hits` = hits +1 WHERE `rid` = '62'

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE `views` SET `hits` = hits+1 WHERE `rid` = '62'' at line 1

I use this active record:
$this->db->set('hits', 'hits+1', FALSE);
$this->db->where('rid', $rid);
$this->db->update('views');

Why am I getting an error in mysql and how can I fix this? I am still searching Google but finding the same update syntax.

[eluser]WanWizard[/eluser]
Why is the syntax of the query in your log different from the one you typed in using PHPMyAdmin? Any why is the query in the error message different as well?

Do you use a prefix? According to your log, you don't. So shouldn't the query be

Code:

EXPLAIN UPDATE `views` SET `hits` = hits+1 WHERE `rid` = ‘62’;

BTW, only reason for this query being slow could be that you don't have an index on the 'rid' column. And if you do, you either have a slow database server, millions or records, or lots of concurrent updates on a MyISAM table.

[eluser]danmontgomery[/eluser]
The error is that explain doesn't work with update statements.

google

[eluser]RaZoR LeGaCy[/eluser]
Hi,

WanWizard, I shortened the table name but forgot to shorten all of the queries. I changed the table to INNODB.

nostrum, Thank you for the clarification of explain.