I have three statements on one table. Two
SELECT * FROM mytable WHERE id_user = :id AND seen IS NULL
SELECT * FROM mytable WHERE id_user = :id AND timestamp > :tm
INSERT INTO mytable (id, id_user, timestamp) VALUE (NULL, :id, :time)
ADD KEY id_user1 (id_user, seen);
ADD KEY id_user2 (id_user, timestamp);
ADD KEY id_user (id_user);
The optimal indexes for the two
selects are the ones given.
select can use either of the indexes. You should test the performance on a reasonably sized set of data to determine if it meets your needs. In all likelihood, one index will meet your needs.
insert will require inserting rows into whatever indexes are assigned, as well as the data tables. This does increase the effort of the insert. But, unless the peak volume of your inserts is at least in the hundreds per second, then you don't need to worry about about the performance.
Computers are pretty fast nowadays and databases are pretty well written. Unless you have a lot of data and a lot of data being loaded, then the
insert performance is probably not an issue.