8

Is it ok if I create like 8 indexes inside a table which has 13 columns?

If I select data from it and sort the results by a key, the query is really fast, but if the sort field is not a key it's much slower. Like 40 times slower.

What I'm basically asking is if there are any side effects of having many keys in the database...

JohnSmith
  • 239
  • 2
  • 5

7 Answers7

8

Creating indexes on a table slows down all write operations on it a little, but speeds up read operations on the relevant columns a lot. If your application is not going to be doing lots and lots of writes to that table (which is true of most applications) then you are going to be fine.

Jon
  • 428,835
  • 81
  • 738
  • 806
  • So it doesn't slow down read operations at all? Just writing? – JohnSmith Dec 09 '11 at 23:00
  • @JohnSmith: Yes, just writing. If you ask for a read that involves only 1 of your 8 indexes, the query planner will simply ignore the other 7. – Jon Dec 09 '11 at 23:07
  • Will the writes get progressively slower as the rows increase? – JohnSmith Dec 09 '11 at 23:14
  • Undoubtedly, but I expect the penalty to increase sub-linearly with the number of rows. Anyway, if you have a table with millions of rows you should get your database advice from someone better at it than me. :-) – Jon Dec 09 '11 at 23:17
  • no, not even close to millions :) It will probably never exceed 20-25K records. But still, selecting 20 rows from 20K, ordered by a non-key field takes like 0.7s, but if I make the field a key it goes down to almost nothing - 0.02 and less – JohnSmith Dec 09 '11 at 23:20
  • There is a very good book on exactly how much cost you add to updates when you add an index: http://www.amazon.com/Relational-Database-Index-Design-Optimizers/dp/0471719994 Short version: nowhere near as much as the legends say. Add the indexes you need. –  Dec 11 '11 at 18:25
4

Don't create indexes that are redundant or unused. But do create indexes you need to optimize the queries you run.

You choose indexes in any table based on your queries. Each query may use a different index, so it pays to analyze your queries carefully. See my presentation MENTOR Your Indexes. I also cover similar information in the chapter on indexing in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

There is no specific rule about how many indexes is too many. In Oracle SQL Tuning Pocket Reference, author Mark Gurry says:

My recommendation is to avoid rules stating a site will not have any more than a certain number of indexes. The bottom line is that all SQL statements must run acceptably. There is ALWAYS a way to achieve this. If it requires 10 indexes on a table, then you should put 10 indexes on the table.

There are a couple of good tools to help you find redundant or unused indexes for MySQL in Percona Toolkit: http://www.percona.com/doc/percona-toolkit/pt-duplicate-key-checker.html and pt-index-usage.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
2

This is a good question and everyone who works with mysql should know the answer. It is also commonly asked. Here is a link to one of them with a good answer:

Indexing every column in a table

Community
  • 1
  • 1
Kai Qing
  • 18,793
  • 5
  • 39
  • 57
2

In a nutshell, each new index requires space (especially if you use InnoDB - see the "Disadvantages of clustering" section in this article) and slows down INSERTs, UPDATEs and DELETEs.

Only you are in a position to decide whether speedup you'll get in SELECT and the frequency with which it will be used is worth it. But whatever you eventually decide, make sure you base your decision on measurement, not guessing!

P.S. INSERTs, UPDATEs and DELETEs with WHERE can also be sped-up by index(es), but that's another topic...

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
2

The cost of an index in disk space is generally trivial. The cost of additional writes to update the index when the table changes is often moderate. The cost in additional locking can be severe.

It depends on the read vs write ratio on the table, and on how often the index is actually used to speed up a query.

Indexes use up disc space to store, and take time to create and maintain. Unused ones don't give any benefit. If there are lots of candidate indexes for a query, the query may be slowed down by having the server choose the "wrong" one for the query.

Use those factors to decide whether you need an index.

It is usually possible to create indexes which will NEVER be used - for example, and index on a (not null) field with only two possible values, is almost certainly going to be useless.

You need to explain your own application's queries to make sure that the frequently-performed ones are using sensible indexes if possible, and create no more indexes than required to do that.

You can get more by following this links: For mysql: http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-are-advantages-and-disadvantages-of-indexes-in-MySQL

For DB2: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0005052.htm

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
1

Indexes improve read performance, but increase size, and degrade insert/update. 8 indexes seem to be a bit too many for me; however, it depends on how often you typically update the table

a1ex07
  • 36,826
  • 12
  • 90
  • 103
1

Assuming MySQL from tag, even though OP makes no mention of it.

You should edit your question and add the fact that you are conducting order by operations as well (from a comment you posted to a solution). order by operations will also slow down queries (as will various other mysql ops) because MySQL has to create a temp table to accomplish the ordered result set (more info here). A lot of times, if the dataset allows it, I will pull the data I need, then order it at the application layer to avoid this penalty.

Your best bet is to EXPLAIN your most used queries, and check your slow query log.

Mike Purcell
  • 19,847
  • 10
  • 52
  • 89