query takes 70 ms to execute

I have an MySQL table named i_visited structured like: userid,tid,dateline

And I run this condition in view_thread.php page:

if (db('count','SELECT userid FROM i_visited 
                WHERE tid = '.intval($_GET['id']).' 
                  AND userid = '.$user['id']))

  mysql_query('UPDATE i_visited 
               SET dateline = 开发者_StackOverflow中文版unix_timestamp(now()) 
               WHERE userid = '.$user['id'].' 
                 AND tid = '.intval($_GET['id']));
else 
  mysql_query('INSERT INTO i_visited (userid,tid,dateline) VALUES 
              ('.$user['id'].','.intval($_GET['id']).',unix_timestamp(now()))');

The problem is that it executes in 80/100 ms (on Windows) 40/60 (on Linux)

1 row affected. (query executed in 0.0707 sec)

The mysql_num_rows() aka db('count',sql) uses 2 / 3 ms, so the problem is at the update and the insert.

P.S. i_visited is an utf8_unicode_ci (InnoDB), has anyone seen this problem?

Other queries run normal (2 / 3 milliseconds)

CREATE TABLE i_visited ( 
  userid int(10) NOT NULL, 
  tid int(10) unsigned NOT NULL, 
  dateline int(10) NOT NULL, 
  KEY userid (userid,tid), 
  KEY userid_2 (userid), 
  KEY tid (tid) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

---

You do not need to do a select to check existence and then choose either Update or Insert.

You can use MySQL's ON DUPLICATE KEY UPDATE Feature like this.

$query = 'INSERT INTO 
            i_visited (userid,tid,dateline) 
            VALUES (' . 
                $user['id'] . ',' . 
                intval($_GET['id']) . ',
                unix_timestamp(now())) 
            ON DUPLICATE KEY UPDATE 
                dateline = unix_timestamp(now())';
mysql_query($query);

This query will insert a new row if there is now KEY conflict, and in case a duplicate key is being inserted, it will instead execute the update part.

And as you have a KEY userid (userid,tid) in your CREATE Statement the above query is equivalent to your if...else block.

Try this and see if there are any gains

You can also use REPLACE INTO, as there are only the specified 3 columns, like this

$query = 'REPLACE INTO 
            i_visited (userid,tid,dateline) 
            VALUES (' . 
                $user['id'] . ',' . 
                intval($_GET['id']) . ',
                unix_timestamp(now()))';
mysql_query($query);

But I would suggest looking at ON DUPLICATE KEY UPDATE as it is more flexible, as it can be used on a table with any number of columns, whereas REPLACE INTO would only work in some limited cases as other column values would also need to be filled in the REPLACE INTO statement unnecessarily

---

I think (part) of the problem is that your table does not have an explicit primary key.
You've only declared secondary keys.

Change the definition to:

CREATE TABLE i_visited ( 
  userid int(10) NOT NULL, 
  tid int(10) unsigned NOT NULL, 
  dateline int(10) NOT NULL, 
  PRIMARY KEY userid (userid,tid), <<----------
  KEY userid_2 (userid), 
  KEY tid (tid) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

InnoDB does not work well without an explicit primary key defined.