Could it be ok basically create like 8 indexes in the table that has 13 posts?
Basically choose data from this and sort the outcomes with a key, the totally really fast, but when the type area isn't a key it's much reduced. Like 40 occasions reduced.
What I am essentially asking is that if you will find any unwanted effects of getting many secrets within the database...
Creating indexes on the table slows lower all write procedures onto it just a little, but accelerates read procedures around the relevant posts a great deal. In case your application won't do numerous of creates to that particular table (which will also apply to most programs) then you will be fine.
Don't create indexes which are redundant or unused. But do create indexes you have to optimize the queries you take.
You select indexes in almost any table according to your queries. Each query could use another index, therefore it pays to evaluate your queries carefully. See my presentation MENTOR Your Indexes. I additionally cover similar information within the chapter on indexing in my opinion SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
There's no specific rule about the number of indexes is simply too many. In Oracle SQL Tuning Pocket Reference, author Mark Gurry states:
My recommendation would be to avoid rules stating a website won't have any greater than a certain quantity of indexes. The end result is that SQL claims must run acceptably. There's ALWAYS a method to accomplish this. Whether it requires 10 indexes on the table, then you definitely should put 10 indexes up for grabs.
You will find a few good tools that will 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.
This is an excellent question and everybody who works together with mysql ought to know the solution. It's also generally requested. This is a connect to one of these with a decent answer:
The bottom line is, each new index requires space (particularly if you use InnoDB - begin to see the "Disadvantages of clustering" section in this article) and slows lower Card inserts, UPDATEs and Removes.
Only you're in a position to determine whether speedup you will get in Choose and also the frequency that it will likely be used makes it worth while. But anything you eventually decide, make certain you base your choice on measurement, not speculating!
P.S. Card inserts, UPDATEs and Removes with WHERE may also be sped-up by index(es), but that is another subject...
Indexes improve read performance, but increase size, and degrade place/update. 8 indexes appear to become a little a lot of for me personally however, it is dependent how frequently you normally update the table
Presuming MySQL from tag, despite the fact that OP will not make any reference to it.
You need to edit your question and add because you are performing
order by procedures too (from the comment you published to some solution).
order by procedures will even decelerate queries (as will many other mysql operations) because MySQL needs to produce a temp table to complete the purchased result set (more information here). Lots of occasions, when the dataset enables it, I'll pull the information I want, then order it in the application layer to avert this penalty.
Your best choice would be to EXPLAIN your most used queries, and appearance your slow query log.