Stack Stack - 2 years ago 115
MySQL Question

Is adding multiple indexes on one table harmful for inserting?

I have three statements on one table. Two

queries and one

// 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
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
statements. I mean an index makes
slow. So I want to know, should I just add an index on
, 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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download