Re: New to design; how to optimize 3 column single table with high I/O?

[Rick James]

> per user per event
Eh? I don't see any column for "event"?

Can you really guarantee that you won't insert two rows with the same millisecond? Of so, then

CREATE TABLE foo (
    userid MEDIUMINT UNSIGNED NOT NULL, 
    timestmp BIGINT UNSIGNED NOT NULL, -- or DOUBLE
    state  ENUM('yes', 'no') NOT NULL,
    PRIMARY KEY(userid, timestmp)   -- Here's your index.
) ENGINE=InnoDB;

CREATE TABLE Users (
    userid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ...,  -- info about the user
    PRIMARY KEY (userid)
) ENGINE=InnoDB;

Any query that includes WHERE userid=... will be quite efficient. In particular, for
> For User X at Time X, Yes or No?

SELECT state
    FROM foo
    WHERE userid = ...
      AND timestmp <= ...
    ORDER BY timestmp DESC
    LIMIT 1;

Very efficient.

If you are starting with a username, then a JOIN would be indicated.