Menu
I have 10mln rows in my table in MySQL and 7 indexes on this table. Now when I try to add 8th it takes infinite time to do this. Is there any way to workaround this problem to add easily and fast 8th index?
982
How to create and optimize SQL Server indexes for better performance 1 Available index types. In general, SQL Server supports many types of indexes but in this article, we assume the reader has a general understanding of the index types available in ... 2 Creating an index. ... 3 Columnstore index. ...
Although indexes are supposed to make queries faster, they cannot absolve you from the sins you committed while writing the queries. Also, creating indexes comes with a cost, which is something, if not thought about, can kill database performance in all aspects and not just reading data. Slow query issues are seen more often with large tables.
This can drastically improve IO patterns (more sequential IO, and less fragmentation in the final index). Build the index into a fresh (presized) filegroup. A fresh filegroup is not fragmented so you can at least get rid of that problem. Use instant file initialization. Or, load the data correctly sorted in the first place.
You can speed things up by disabling the non-clusterd indexes, then manually building them after the clustered index has been rebuilt. You'll want to make sure that end users aren't able to touch the system while the non-clustered indexes are being built as their query performances will be awful until the non-clustered indexes are created.
No, the time it takes to create an index is directly proportional to the amount of data you have. In MS SQL I create indexes on a table with that many records in about 10 minutes.
EDIT: After seeing comment, please elaborate on infinite. By definition you are saying it never finishes, my answer is related to a long running index creation and not infinite.
185
This is one of the thousand ways MySQL just sucks. By design...
Details: http://lists.mysql.com/mysql/202489
and I don't care if I lose karma for this answer.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
