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?
+5
A:
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.
Dustin Laine
2010-05-24 21:13:31
Yes, it took 15 minutes
tomaszs
2010-05-24 21:40:41
Then you are about as good as it gets. Indexing is a very intensive task, especially with a lot of records.
Dustin Laine
2010-05-24 21:54:08
+2
A:
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.
Marco Mariani
2010-05-24 21:21:57
I was shocked when I saw that executing an ALTER TABLE to change the default value - or nullability - of a column, was performed by copying the whole table and recreating all the indexes. These things take very little time on other DMBSs; the recommended way to do it on MySQL for large tables was to muck around with the filesystem - changing the table definition with an empty data file and putting the full data file back in place..
Marco Mariani
2010-05-24 21:49:35
The poster of this article is completely wrong. MySQL does rebuild tables in many cases when you do ALTER TABLE, but it never rebuilds the table more than once per ALTER statement; moreover, you can do an arbitrary number of modifications with a single ALTER if desires.
MarkR
2010-05-24 22:24:13
Nice to know. Indeed, I group my ALTER statements when I can. Still, the fact that *dropping* an index can require several GB and hours, is not pretty.
Marco Mariani
2010-05-25 06:46:53
A:
Which engine are you using? The implementation of ALTER TABLE ... CREATE INDEX varies significantly.
Using MyISAM, any index change requires a complete table rebuild. This is not inherent to the engine, but a bug which has never been fixed.
Using InnoDB, creating a secondary index does not require a table rebuild, but this optimisation is only available if you're using the InnoDB plugin (rather than the older, shipped engine). Changing the primary key always requires a rebuild, because it is clustered.
Rebuilding the table (in either case) requires a lot of work as it must rebuild all the existing indexes, as well as rewriting the rows, in order to complete the operation. If your table fits in RAM (10M rows sounds like it should do easily), this is relatively quick.
Rebuilding a table which doesn't fit in ram is rather expensive, I recommend it's avoided if possible.
Rebuilding an individual index which doesn't fit in ram, is VERY expensive and is best avoided.
How often do you need to add new indexes? Perhaps you can populate the tables with the index already created?
MarkR
2010-05-24 22:21:59