Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column?

There is a FULLTEXT index on the TEXT column, which is very essential for my searching operation.

share|improve this question
1  
Approximately how big 'huge' is in the terms of row number? Also, a table definition always helps. –  dezso Jan 31 at 7:29
    
1.5 million records with actual .txt files saved in the table. –  Nikhil Jan 31 at 7:32
    
@Nikhil I have posted an answer, below, that directly answers the question you asked. It seems possible, though, that you are actually asking a different question, related to performance problems you are seeing on a full text index. If that is the case, we definitely need more information, such as the table definition and the output of EXPLAIN SELECT from a poorly-performing query. –  Michael - sqlbot Jan 31 at 12:35

1 Answer 1

You can't partition an InnoDB table with a fulltext index, which would make intuitive sense, since it's difficult to envision how fulltext searches on (what would presumably be implemented as) multiple underlying fulltext indexes (one for each partition) would be able to give meaningful relevance and query expansion results.

Full-text search (FTS) and foreign keys are not supported by InnoDB partitioned tables. For more information, see Section 12.9.5, “Full-Text Restrictions” and Section 18.6.2, “Partitioning Limitations Relating to Storage Engines”.

http://dev.mysql.com/doc/refman/5.6/en/online-ddl-partitioning.html

The limitation applies to all storage engines.

http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-storage-engines.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.