+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL Set Increment Field plus 1 (/showthread.php?tid=31311)
MySQL Set Increment Field plus 1 - El Forum - 06-14-2010
[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.
MySQL Set Increment Field plus 1 - El Forum - 06-14-2010
[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.
MySQL Set Increment Field plus 1 - El Forum - 06-14-2010
[eluser]danmontgomery[/eluser]
The error is that explain doesn't work with update statements.
MySQL Set Increment Field plus 1 - El Forum - 06-14-2010
[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.