Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

share|improve this question
    
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
show 3 more comments

1 Answer 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.

share|improve this answer
    
So, if I understand you correctly, you're saying that I should add a column defined as date(<my datetime column>) and index that, and then start all my joins with that column. Correct? –  Geoff Canyon Dec 6 '12 at 16:51
    
That would only be a start. Primary Keys and Unique Keys provide more concise mechanisms for key selection. If the date column is not unique to any one partition, you could get multiple table scans or multiple index scans. Please provide the SHOW CREATE TABLE mrgtablename\G and insert the output into your question (not in the comments) so I can better clarify my answer. –  RolandoMySQLDBA Dec 6 '12 at 16:57
add comment

Your Answer

 
discard

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.