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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
Approximately how big 'huge' is in the terms of row number? Also, a table definition always helps. – dezso Jan 31 '14 at 7:29
1.5 million records with actual .txt files saved in the table. – Nikhil Jan 31 '14 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 '14 at 12:35

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”.

The limitation applies to all storage engines.

share|improve this answer

Your Answer


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.