SQL query is much faster if I create indexes

Question

Ok if i create like 8 indexes inside a table with 13 columns?

If I select data from it and sort the results by keyword, the query will be very fast, but if the sort field is not key, it will be much slower. Like 40 times slower.

Basically I ask the question if there are any side effects associated with having many keys in the database ...

Answer 1

Creating indexes on a table slows down all write operations on it a little, but significantly speeds up read operations in the corresponding columns. If your application does not make many, many entries in this table (which is true for most applications), then you will be fine.

Answer 2

Do not create redundant or unused indexes. But create the indexes needed to optimize your queries.

You select indexes in any table based on your queries. Each query may use a different index, so it carefully analyzes your queries. See My Presentation MENTOR Your Indexes . I will also talk about similar information in the indexing chapter in my SQL Antipatterns book : Avoid Database Programming Errors .

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

My recommendation is to avoid the rules pointing to the site, there will be no more than a certain number of indexes. The bottom line is that all SQL statements should work reasonably well. There is ALWAYS a way to achieve this. If a table requires 10 indexes, then you should put 10 indexes on the table.

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

Answer 3

This is a good question, and anyone working with mysql should know the answer. This is also often asked. Here is a link to one of them with a good answer:

Indexing each column of a table

Answer 4

In a nutshell, each new index takes up space (especially if you use InnoDB — see the “Disadvantages of Clustering” section in this article ) and slows down INSERT, UPDATE, and DELETE.

Only you can decide if the acceleration will fall into SELECT, and the frequency with which it will be used is worth it. But no matter what you ultimately decide, make sure that you base your decision on measurement, not guessing!

PS INSERT, UPDATE and DELETE with WHERE can also be accelerated by indexes (es), but that's another topic ...

Answer 5

The cost of an index on disk space is usually trivial. The cost of additional records to update the index when changing the table is often moderate. The cost of additional blocking can be serious.

This depends on the read / write relationship in the table and how often the index is used to speed up the query.

Indexes use disk space for storage and take time to create and maintain. Unused do not bring any benefit. If there are many candidate indexes for the query, the query may be slowed down if the server chose "wrong" for the query.

Use these factors to decide if you need an index.

You can usually create indexes that NEVER will be used - for example, and an index in a (non-zero) field with two possible values ​​will almost certainly be useless.

You need to explain your own application requests to make sure that frequently executed ones use reasonable indexes as much as possible and do not create more indexes than is required for this.

You can get more by following these 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

Answer 6

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

Answer 7

Assuming MySQL from the tag, although the OP does not mention it.

You should edit your question and add the fact that you are doing order by operations (from the comment sent to the solution). order by operations also slow down queries (like other other mysql statements) because MySQL must create a temporary table to execute an ordered set of results (more info here ). Many times, if the data set allows this, I will pull out the necessary data and then order it at the application level to avoid this penalty.

It is best to use the EXPLAIN most frequently used queries and check the slow query log.