Is adding multiple indexes on one table harmful for inserting?

Question

I have three statements on one table. Two SELECT queries and one INSERT query.

// first
SELECT * FROM mytable WHERE id_user = :id AND seen IS NULL

// second
SELECT * FROM mytable WHERE id_user = :id AND timestamp > :tm

// third
INSERT INTO mytable (id, id_user, timestamp) VALUE (NULL, :id, :time)

AS you see, based on those two first SELECT queries, I need these two indexes:

ADD KEY id_user1 (id_user, seen);
ADD KEY id_user2 (id_user, timestamp);

All fine. But I heard indexes are harmful for INSERT, UPDATE statements. I mean an index makes INSERT, UPDATE slow. So I want to know, should I just add an index on id_user, Something like this:

ADD KEY id_user (id_user);

Actually I'm trying to make a fast selecting and inserting. So what kind of indexes should I add based on those three statements?

Answer 1

Well, that's a very general question, it is data dependent . An index can harm, can help but you can't always predict it before trying to run it. You can look at the execution plan before and after adding the index to decide better.

In general, index harm performances of INSERT statements, they usually help UPDATE statements and not harm them(again, depend on the index and on the update statement) .

To decide which indexes you need to add,you need to review what exactly will you do on this table on daily basis, if its mostly updates, then you should defiantly add the indexes on the columns of this update.

We can't supply an answer that will be correct for you, unless you ask a specific question including tables design and required mission, so if you have one, update your question and post it.