在mysql中使用ENGINE作为MRG_MYISAM是否有任何优势

Question

I am doing a tracking site like google adwords. I am doing only modifications. In that site i have seen they are creating tables for each month and merging that table into one this is done for the tables which stores information about clicks and search details and the merged tables having crores of records.And for querying they have used only the merged table which is having crores of records. Is there any advantage of using tables like this?And the query is taking more than 10 minutes to execute.

Answer 1

A MRG_MYISAM only works over MyISAM tables, which are, by themselves, not the first option for a table. You would normally go for InnoDB tables.

The MRG_MYISAM engine was invented before MySQL had support for views and for partitions. Range partitioning (e.g. partition per month) is most probably what you want.

Partitioning is transparent to the user in terms of queries, but nevertheless uses pruning so as to only read from selected partitions for a query, thus optimizing it.

I would recomment that you use InnoDB tables, and check out range partitioning. MRG_MYISAM and MyISAM are still in use. They could work out for you. It's just that MyISAM introduces so much trouble (no crash recovery, table level locking, more...) that it's many times out of the question.