I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will only join to rows in B_2012-12-05, and rows in A_2012-12-06 will only join to rows in B_2012-12-06, etc. Is there a way to write an intelligent query so that MySQL won't go looking for rows in B_2012-12-06 to join with rows in A_2012-12-05?

Edit by RolandoMySQLDBA 11:17 EDT

Please insert the output of these into this section:

They all look pretty much like this:

CREATE TABLE `mrg_31_session` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `phpsessionid` varchar(32) NOT NULL,
  `start_datetime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_phpsessionid` (`phpsessionid`),
  KEY `ix_startdatetime` (`start_datetime`),
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 UNION=(`session_20121205`,`session_20121204`,`session_20121203`,`session_20121202`,`session_20121201`,`session_20121130`,`session_20121129`,`session_20121128`,`session_20121127`,`session_20121126`,`session_20121125`,`session_20121124`,`session_20121123`,`session_20121122`,`session_20121121`,`session_20121120`,`session_20121119`,`session_20121118`,`session_20121117`,`session_20121116`,`session_20121115`,`session_20121114`,`session_20121113`,`session_20121112`,`session_20121111`,`session_20121110`,`session_20121109`,`session_20121108`,`session_20121107`,`session_20121106`,`session_20121105`)

  • What do you mean by a merge table? Is Rolando right in his assumption? And (mainly for curiosity) how many rows are there per partition? – dezso Dec 5 '12 at 23:34
  • Please run SHOW CREATE TABLE A_2012-12-05 \G and post its output in the question body. Are all the tables using the MyISAM storage engine? – RolandoMySQLDBA Dec 6 '12 at 16:14
  • Here's how the merge table is defined: CREATE TABLE table_a ( <a bunch of columns and indexes> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 UNION=( <a bunch of tables> ); – Geoff Canyon Dec 6 '12 at 16:30
  • Sure would be nice if returns worked in comments... – Geoff Canyon Dec 6 '12 at 16:31
  • The resulting merge table has about 400 million rows and is 188GB. The table I'm trying to join it to is 39 million rows and 40GB – Geoff Canyon Dec 6 '12 at 17:00

When you say merge table, I assume you mean MERGE (Mrg_MyISAM) storage engine.

Without seeing any table definitions, I can easily say this...

The smartest way would be to have each MyISAM table defined with a very good primary key/unique key where the lead column would be the date. At the very least, create an index that uniquely identifies data with only one partition (one underlying MyISAM table).

Each MyISAM table under the Merge definition would have such an index.

Any other index where the date is not the first column would result in bunches of table scans (NOT PRETTY !!!)

I have two posts I made back in January 2012 on MERGE table usage:

While the Mrg_MyISAM storage engine is still around, most would favor creating a Partitioned Table instead. Notwithstanding, the same rule applies: create an index that uniquely identifies data with only one partition.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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