I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. However, this session table is showing up in the slow query log when we trim old records:

# Time: 120719 10:05:11
# User@Host: xxx[xxx] @  [10.x.x.x]
# Thread_id: 369051896  Schema: forumdb  Last_errno: 0  Killed: 0
# Query_time: 8.352811  Lock_time: 0.000028  Rows_sent: 0  Rows_examined: 19635  Rows_affected: 19635  Rows_read: 0
# Bytes_sent: 13  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
SET timestamp=1342710311;
    WHERE lastactivity < 1342709401;

I've confirmed there is a BTREE index on the lastactivity table:

mysql> SHOW INDEX FROM session FROM forumdb;
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
| session |          0 | PRIMARY      |            1 | sessionhash  | NULL      |       78941 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid       |            1 | userid       | NULL      |       26313 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | idhash       |            1 | idhash       | NULL      |        8771 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_2     |            1 | userid       | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_2     |            2 | lastactivity | NULL      |       39470 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_3     |            1 | userid       | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_3     |            2 | host         | NULL      |       39470 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | lastactivity |            1 | lastactivity | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
8 rows in set (0.00 sec)

I'm curious why the delete query is taking so long with so few records.

It should be noted that this table is utilized very heavily with so many users online at the same time. We have some heavy hardware sitting behind it.

Any ideas on what I can do to speed up this process? It appears to be locking the table so that I see high load across my cluster when this delete function is taking place.

Query cache is turned off (we use memcache). Here is relevant portions of my.cnf:


query_cache_size= 32M
query_cache_type = 0

Finally, here is some more info on the table:

Table info


After changing table over to InnoDB (at 17:15 in the chart below) I am seeing much better performance and no more DELETE in the slowlogs for this table. The whole cluster is seeing better performance since making this change. Thank you.

CPU Results

  • What are your settings for query_cache_size and query_cache_type? (I can't remember whether query cache invalidation is included within the query time in the slow query log but setting the query cache too large has bitten me before.)
    – Ladadadada
    Commented Jul 19, 2012 at 15:26
  • query_cache_limit=1M query_cache_size= 32M query_cache_type = 0 (It is turned off) Commented Jul 19, 2012 at 15:29
  • Which version of MySQL? Commented Jul 19, 2012 at 16:12
  • I love fast DELETE questions like this. +1 !!! Commented Jul 19, 2012 at 16:41

3 Answers 3


I would probably convert your Memory table to InnoDB


for your situation. Memory tables shine for low writes, many-reads that you don't care if you lose the data (loading configuration values, for instance). But when you start writing a lot, the table locks kill you if you have many connections accessing it:

Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions. [src]

Tracking of session data may seem like a good candidate because of not caring of the data-loss, but I think you might gain significant benefit of removing table locks by switching to InnoDB in this case.


Memory tables require allocation of heap memory and they reuse that memory for deleted rows.

"Deleted rows are put in a linked list and are reused when you insert new data into the table." - http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html

My best guess is that with 19k rows, the 8 seconds is being spent managing the linked list data structure behind the scenes that is being handed the deleted rows.

If you remember from freshman comp sci, a list insertion requires the next pointer of the last element to be updated to point to the new element and the tail pointer of the list to be updated to the new element, as well as the list count. I can imagine that 19k x those takes some cycles, plus removing the data and swapping the memory.

If you delete 19 rows is it faster by a factor of 1000 times? This could be explained as an (O)n linear cost.

  • 1
    Seems inefficient for larg(er) tables. Thank you for the explanation. Commented Jul 20, 2012 at 13:41

Maybe you can do a temp table bait-and-switch

ALTER TABLE session RENAME sessionold;
CREATE TABLE sessionnew LIKE sessionold;
INSERT INTO sessionnew SELECT * FROM sessionold WHERE lastactivity >= 1342709401;
ALTER TABLE sessionnew RENAME session;
DROP TABLE sessionold;

This is a faster DELETE because

  • this takes the session table away from everyone to perform this DELETE. Since MEMORY tables use full table locks like MyISAM, a heavily used MEMORY table would be cumbersome to do any DML because of the constant disk I/O (each access of a MEMORY table must always read the .frm).
  • with the session table isolated from users, you can copy everything for lastactivity >= 1342709401 with any I/O against it.
  • the table becomes available again only with records to be kept

Give it a Try !!!

UPDATE 2012-07-19 12:38 EDT

Here is a better alternative:

CREATE TABLE sessionnew LIKE session;
ALTER TABLE session RENAME sessionold;
ALTER TABLE sessionnew RENAME session;
INSERT INTO session SELECT * FROM sessionold WHERE lastactivity >= 1342709401;
DROP TABLE sessionold;

This should work even faster because the first three(3) lines will empty out the session table immediately. Then, whatever rows have lastactivity >= 1342709401 from the old session table is brought over. The old session table is then dropped.

  • I would think for the time period that these commands are running, it would make the site unusable since the sessions table wouldn't be available. Sort of the same issue we are facing right now. Commented Jul 19, 2012 at 15:43
  • How often is the DELETE to be executed ??? Commented Jul 19, 2012 at 16:30
  • It is run every 5 minutes, so unfortunately this does not work for us. But thank you for your detailed answer and definitely an alternative if you need to use a memory table (for example, if your disk i/o is slow - in our case we are on a RAID SSD array). Commented Jul 20, 2012 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.