is there a limit of merge tables with Mysql ?
- by sysko
I'm working on a database with mysql 5.0 for an open source project
it's used to stored sentences in specific languages and their translations in other languages
I used to have a big table "sentences" and "sentences_translations" (use to join sentences to sentences) table but has we have now near one million entries, this begin to be a bit slow, moreover, most of request are made using a "where lang ="
so I've decided to create a table by language
sentences_LANGUAGECODE
and sentences_translation_LANGSOURCE_LANGTARGET
and to create merge table like this
sentences_ENG_OTHERS which merge sentences_ENG_ARA sentences_ENG_DEU etc...
when we want to have the translations in all languages of an english sentence
sentences_OTHERS_ENG
when we want to have only the english translations of some sentences
I've created a script to create all these tables (they're around 31 languages so more than 60 merge table), I've tested, that works really great a request which use to take 160ms now take only 30 :)
but I discover that all my merge table after the 15th use to have "NULL" as type of storage engine
instead of MRG_MYISAM, and if delete one, then I can create an others, using FLUSH table between each creation also allow me to create more merge tables
so is this a limitation from mysql ? can we override it ?
thanks for your answers
