mbabuskov - 5:23 pm on Mar 4, 2011 (gmt 0)

- MyISAM
- PK is composed of 3 columns: mediumint,date,tinyint

Everything I wrote in initial post is about MyISAM. Since MyISAM uses table-lock instead of row-lock like InnoDB, locking out concurrent selects while inserting can make a huge difference in performance.

Most of the queries used all three components of primary key, but the 2nd and 3rd field are often queried in range. Table looks like this:

create table points (
userid mediumint,
playdate date,
game tinyint,
points integer,
data varchar(12))

Most queries are like:

select * from points where userid = ? and playdate = ? and game = ?

OR

select * from points where userid = ? and playdate between ? and ?

Deleted records are mostly closer to the end of the table (in the last 1000 records or so).

OPTIMIZE TABLE runs way too slow to run it every day. Maybe weekly would be possible.

Thanks.