1

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`)

8
  • 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? Dec 5, 2012 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? Dec 6, 2012 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> ); Dec 6, 2012 at 16:30
  • Sure would be nice if returns worked in comments... Dec 6, 2012 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 Dec 6, 2012 at 17:00

1 Answer 1

1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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