mysqlperformancememory-table

Simple constant select on primary key in small MEMORY table very slow

I have a MEMORY table with about 650 rows, 5 MB data length, 60 kB index length (so it's pretty small). It has one SMALLINT primary (hash) key, and about 90 other columns (ints, varchars, datetimes, no blobs or texts). (EDIT: there's also a hash key on a BIGINT column.)

I'm running this query (from PHP) quite often (about 10 times per second):

select * from userek where id={CONST_ID} and kitiltva=0 and kitiltva_meddig<"{CONST_DATETIME}" and inaktiv=0

Note: id is the primary key. I need the * because the result is used in a lot of different places, and basically all columns are used here or there.

My problem is: the query gets abnormally slow on a regular basis. About 0.5s on average, 8s max. Most of the times it's very fast: 75% of runs faster than 3ms, 85% faster than the average. But 15% it's slower than average, 13% slower than 1s. So it's got a long tail.

And I have absolutely no idea what might cause it. Any thoughts anyone?

Solution

  • Sorry for answering my own question, but at least I have an answer. I try to write it in a such a way that's helpful for others.

    Since it's a MEMORY table, I excluded I/O problems.

    Next, the query is a simple (const) select by the primary key, so it cannot be an indexing problem either.

    The next guess was locking. There are/were some very slow selects in my application on this table. And it can be a problem: slow selects delay updates that delay other selects, so in the end this very simple and fast select can be delayed.

    I checked the slow query log and found two frequent and slow selects that were using this particular table (and others as well). The cause was a badly formed join on a case:

    A left join B on case
when A.x=1 then B.id=A.id2
when A.x=2 then B.id=A.id3
else B.id=0
end

    instead of

    A left join B on B.id = case
when A.x=1 then A.id2
when A.x=2 then A.id3
else 0
end

    Both give the same result, but the latter can use an index of B.id, the former cannot.

    Once I corrected these queries the performance of the original query was greatly enhanced: 5ms instead of 500ms on average. And 98% faster than average.

    The moral: