StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql Slow Insert
text
Body
copied!<p>I have the following InnoDB table:</p> <pre><code>+-----------+-----------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | doc_id | char(32) | NO | | NULL | | | staff | char(18) | NO | | NULL | | | timestamp | timestamp | NO | MUL | CURRENT_TIMESTAMP | | +-----------+-----------+------+-----+-------------------+----------------+ </code></pre> <p>With these keys:</p> <pre><code>+--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | staff_online | 0 | PRIMARY | 1 | id | A | 277350 | NULL | NULL | | BTREE | | | staff_online | 1 | timestamp | 1 | timestamp | A | 277350 | NULL | NULL | | BTREE | | | staff_online | 1 | staff_timestamp | 1 | timestamp | A | 277350 | NULL | NULL | | BTREE | | | staff_online | 1 | staff_timestamp | 2 | staff | A | 277350 | NULL | NULL | | BTREE | | +--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre> <p>I just noticed that <code>in mysql-slow.log</code> I sometimes have an INSERT query on this table which takes more than 1 second</p> <pre><code>INSERT INTO `staff_online` (`doc_id`, `staff`, `timestamp`) VALUES ('150b60a0ab8c5888bdbbb80bd8b7f8a2', 'asia', '2011-01-29 16:52:54') </code></pre> <p>I'm really puzzled why it takes so long. How can I speed it up?</p> <p>BTW: Each day there're ~80 slow INSERTS and 40 slow UPDATES like this.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload