Re: UNABLE TO INSERT DATA IN ARCHIVE ENGINE HAVING TABLE WITH 1015 PARTITIONS

[Jonathan Stephens]

My recommendations are:

1. Use DATETIME instead of TIMESTAMP. You're not going to be able to get any of the benefits of partition pruning using TIMESTAMP. (What's so hard about doing an ALTER TABLE?)

2. Don't worry about the subpartitioning, at least not at this point. Work out a partitioning scheme that gives you good results with a subset of your data, THEN worry about subpartitions.

3. If your current database server OS can't provide what you need, consider moving it to a different one.

4. Consider using a different storage engine:

4a. If you don't need transaction support, then MyISAM is probably going to give you the best performance.

4b. ARCHIVE does save space, but it supports only a single AUTO_INCREMENT index on each table. You could do a lot to improve your performance with the right indexes.

5. You could do a lot to improve performance with the right indexes. (Wow, is there an echo in here?) Huge tables with no indexes is just not a very good idea.

Jon Stephens
MySQL Documentation Team @ Oracle

MySQL Dev Zone
MySQL Server Documentation
Oracle