How can a simple MySQL insert/update be slower than an external web request?
<p>Due to some performance issues I've been optimizing several SQL queries and adding indexes to certain tables/columns to speed up things.</p>

<p>Been running some time tests using <code>microtime()</code> in PHP (looping the queries a couple hundred times and calling <code>RESET QUERY CACHE</code> in each loop). I'm somewhat baffled by the results from one of the functions that does 3 things:</p>

<ol>
<li>Inserts a row in a <code>sessions</code> table (InnoDB).</li>
<li>Updates a row in a <code>users</code> table (InnoDB).</li>
<li>Sends session ID to remote server which inserts the session ID in a session table of it's own (MongoDB).</li>
</ol>

<p>Step 1. generally takes 30 - 40 ms, step 2. 20 - 30 ms and step 3. 7 - 20 ms.</p>

<p>I've tried looking up some expected query times for MySQL, but haven't found anything useful, so I don't know what to expect. Having said that, those query times seem somewhat high and I would definite <strong>not</strong> expect the web request to finish faster than the MySQL queries to the local database.</p>

<p><em>Any idea if those query times are reasonable compared to the web request?</em></p>

<p><strong>SQL/system information</strong></p>

<p>Both servers (the remote and the one with the MySQL database) are virtual servers running on the same physical server with shared storage (multiple SSD raid destup). The remote server has a single CPU and 2 GB RAM assigned, the MySQL server has 8 CPUs and 32 GB RAM assigned. Both servers are on the same LAN.</p>

<p>The <code>sessions</code> insert query:</p>

<pre><code> INSERT INTO sessions (
  session_id,
  user_id,
  application,
  machine_id,
  user_agent,
  ip,
  method,
  created,
  last_active,
  expires
)
VALUES (
  string, // session_id
  int, // user_id
  string, // application
  string, // machine_id
  string, // user_agent
  string, // ip
  string, // method
  CURRENT_TIMESTAMP, // created
  CURRENT_TIMESTAMP, // last_active
  NULL / FROM_UNIXTIME([PHP timestamp]) // expires
)
</code></pre>

<p>The <code>sessions</code> table (contains ~500'000 rows);</p>

<pre><code>+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| sessions_id | int(11)       | NO   | PRI | NULL    | auto_increment |
| session_id  | char(32)      | NO   | UNI | NULL    |                |
| user_id     | int(11)       | NO   | MUL | NULL    |                |
| application | varchar(128)  | NO   |     | NULL    |                |
| machine_id  | varchar(36)   | NO   |     | NULL    |                |
| user_agent  | varchar(1024) | NO   |     | NULL    |                |
| ip          | varchar(15)   | NO   |     | NULL    |                |
| method      | varchar(20)   | NO   |     | NULL    |                |
| created     | datetime      | NO   |     | NULL    |                |
| last_active | datetime      | NO   |     | NULL    |                |
| expires     | datetime      | YES  | MUL | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+
</code></pre>

<p>The <code>users</code> update query:</p>

<pre><code>UPDATE users
SET last_active = string // For example '2016-01-01 00:00:00'
WHERE user_id = int
</code></pre>

<p>The <code>users</code> table (contains ~200'000 rows):</p>

<pre><code>+------------------------+---------------------+------+-----+---------+----------------+
| Field                  | Type                | Null | Key | Default | Extra          |
+------------------------+---------------------+------+-----+---------+----------------+
| user_id                | int(11)             | NO   | PRI | NULL    | auto_increment |
| username               | varchar(64)         | NO   | MUL | NULL    |                |
| first_name             | varchar(256)        | NO   |     | NULL    |                |
| last_name              | varchar(256)        | NO   |     | NULL    |                |
| info                   | varchar(512)        | NO   |     | NULL    |                |
| address1               | varchar(512)        | NO   |     | NULL    |                |
| address2               | varchar(512)        | NO   |     | NULL    |                |
| city                   | varchar(256)        | NO   |     | NULL    |                |
| zip_code               | varchar(128)        | NO   |     | NULL    |                |
| state                  | varchar(256)        | NO   |     | NULL    |                |
| country                | varchar(128)        | NO   |     | NULL    |                |
| locale                 | varchar(5)          | NO   |     | NULL    |                |
| phone                  | varchar(128)        | NO   |     | NULL    |                |
| email                  | varchar(256)        | NO   | MUL | NULL    |                |
| password               | char(60)            | NO   | MUL | NULL    |                |
| permissions            | bigint(20) unsigned | NO   |     | 0       |                |
| created                | datetime            | YES  |     | NULL    |                |
| last_active            | datetime            | YES  |     | NULL    |                |
+------------------------+---------------------+------+-----+---------+----------------+
</code></pre>