21

I have a MySQL table that has, among other attributes, a timestamp, a type and a user_id.
All of them are searchable and/or sortable.
Is it better to create an index for each one, or create a single compound index with all three, or both?

Riedsio
  • 9,758
  • 1
  • 24
  • 33
Petruza
  • 11,744
  • 25
  • 84
  • 136

2 Answers2

16

If you are going to perform searches on those fields separately, you will probably need separate indexes to make your queries run faster.

If you have an index like this:

mysql> create index my_idx on my_table(tstamp, user_id, type);

And you query is:

mysql> select * from my_table where type = 'A';

Then my_idx won't be that helpful for your query and MySQL will end up doing a full table scan to resolve it.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • ...and will still be helpfull for queries such as `select * from my_table where tstamp = @ts1` – Unreason Nov 16 '10 at 14:40
  • Yes, it might help to that query. But will not be as helpful as an index **only** on that column. – Pablo Santa Cruz Nov 16 '10 at 15:02
  • 4
    yes in reality it will be as helpful as index only on that column. It might be slower due to the fact that the index is bigger - however, if index on single column would be useful (i.e. high selectivity) then composite index will be as useful - mysql might look through a larger index, but btree indexes are organized as trees, so increase in size, combined with the fact that only a part of the tree needs to be visited, combined with the fact that I/O operations happen in block sizes, translates to: indexes with same starting column are equally helpful for conditions on that column. – Unreason Nov 16 '10 at 15:22
  • 2
    and furthermore, wormed up database will keep indexes in RAM, so it reduces the performance difference even further (here assuming that compound index is justified on its own). In another words - usually when introducing compound indices I tend to drop single column indices of the first column. – Unreason Nov 16 '10 at 15:27
  • @Unreason, "wormed" database? – Pacerier Feb 03 '15 at 07:00
  • Hi thanks for the answer can you tell us whats the type of this index i know its not unique thoght – shareef May 13 '15 at 07:30
10

Pablo's answer is correct, but maybe you'll fail to realize that a compound index might be justified.

You can have multiple indexes and having idx1(tstamp, user_id) does not exclude you from having indx2(tstamp, type) or idx1reverse(user_id, tstamp) and so on...

Compound indexes are most useful when they cover all of the conditions in your query, so the index you propose will be most useful for

SELECT * FROM my_table WHERE tstamp = @ts1 AND user_id = @uid AND type = @type

If you want to improve performance of such queries you can consider adding a composite index.

The downside of the indexes is that it slows down all update operations. However, most general applications do many more selects then updates (both in terms transactions i.e. number of statements and especially in terms of of records affected/retrieved) and at the same time are much more tolerant of slower updates (users mostly judge the speed of the system not by the time it is necessary to update a record, but by the time necessary to retrieve records; again YMMV and there are applications that don't play by such rules).

The best would be if you had some way to test the database performance in terms of typical workloads (create some typical SQL scripts; independent and repeatable, or create unit tests at the application level) and then you can objectively tune your database.

EDIT Also realize that indexes can be added and dropped without affecting the system in terms of functionality. Therefore, you can tune your indexes later, during actual usage of the system - and normally you would collect and profile the slow SQL queries looking for conditions that could benefit from adding indexes.

Unreason
  • 12,556
  • 2
  • 34
  • 50