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...
Source: Tips4all
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.
ReplyDeleteThis 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:
ReplyDeleteIndexing every column in a table
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.
ReplyDeleteOnly 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...
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
ReplyDeleteAssuming MySQL from tag, even though OP makes no mention of it.
ReplyDeleteYou 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.
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.
ReplyDeleteIt 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