CODEWITHSUNDEEP
mysqlsqlindexing
Martin AJ
Martin AJ

Reputation: 6697

Is adding multiple indexes on one table harmful for inserting?

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?

Upvotes: 3

Views: 145

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

The optimal indexes for the two selects are the ones given.

However, either 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.

Your 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.

Upvotes: 1

sagi
sagi

Reputation: 40491

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.

Upvotes: 2

Related Questions