Optimizing DELETE Query on MySQL MEMORY Table

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;
DELETE FROM session
    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 | lasta

I would probably convert your Memory table to InnoDB

ALTER TABLE session ENGINE=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.

StackExchange Database Administrators Q#21168, answer score: 5