MySQL Forums
Forum List  »  InnoDB

Re: Larger than expected tmp files querying InnoDB format database
Posted by: Michael Lueck
Date: July 10, 2014 10:32AM

Greetings Rick,

Thank you for your kind reply. This morning I just wrapped up completely rebuilding thd database in question. I found instructions on the vendor's wiki for validating integrity of the DB schema, and going through that was able to export/import rebuilding the DB from the raw schema while retaining client specific schema additions.

The poor performance and large tmp file issue persists after all of that work! :-(

Oh, and I also discovered a simple export of 2379 contacts ALSO generates a 2GB /tmp file. That query happens to succeed on the new server whereas fails on the legacy/production server.

Quote

I got lost in our/peer/current/production/5.0/5.5/new/etc. Are you saying that 5.5 is generating a bigger tmp table and is taking longer?

The current server runs MySQL 5.0.95
The sever this client is moving to runs MySQL 5.5.37
Both servers have the same performance problems.

A peer on the CiviCRM forum ran the same query against a much larger data set, and had the significantly faster execution time and drastically smaller MySQL tmp file size.

Better?

So, here goes with materials you requested:

The query for the Contribution Dashboard:
SELECT  civicrm_contribution.id,
        contact_a.id as contact_id,
        contact_a.contact_type  as `contact_type`,
        contact_a.contact_sub_type  as `contact_sub_type`,
        contact_a.sort_name  as `sort_name`,
        contact_a.display_name  as `display_name`,
        civicrm_contribution.id as contribution_id,
        civicrm_contribution.currency as `currency`,
        civicrm_contribution.receive_date as `receive_date`,
        civicrm_contribution.non_deductible_amount as `non_deductible_amount`,
        civicrm_contribution.total_amount as `total_amount`,
        civicrm_contribution.fee_amount as `fee_amount`,
        civicrm_contribution.net_amount as `net_amount`,
        civicrm_contribution.trxn_id as `trxn_id`,
        civicrm_contribution.invoice_id as `invoice_id`,
        civicrm_contribution.cancel_date as `cancel_date`,
        civicrm_contribution.cancel_reason as `cancel_reason`,
        civicrm_contribution.receipt_date as `receipt_date`,
        civicrm_contribution.thankyou_date as `thankyou_date`,
        civicrm_contribution.source as `contribution_source`,
        civicrm_contribution.amount_level as `amount_level`,
        civicrm_contribution.is_test as `is_test`,
        civicrm_contribution.is_pay_later as `is_pay_later`,
        contribution_status.value as contribution_status_id,
        civicrm_contribution.check_number as `check_number`,
        civicrm_contribution.campaign_id as contribution_campaign_id,
        civicrm_financial_type.id as financial_type_id,
        civicrm_financial_type.name as financial_type,
        payment_instrument.id as instrument_id,
        payment_instrument.label as `payment_instrument`,
        civicrm_product.id as product_id,
        civicrm_product.name as `product_name`,
        civicrm_product.sku as `sku`,
        civicrm_contribution_product.id as contribution_product_id,
        civicrm_contribution_product.product_option as `product_option`,
        civicrm_contribution_product.fulfilled_date as `fulfilled_date`,
        civicrm_contribution_product.start_date as `contribution_start_date`,
        civicrm_contribution_product.end_date as `contribution_end_date`,
        civicrm_contribution.contribution_recur_id as `contribution_recur_id`,
        civicrm_financial_account.id as financial_account_id,
        civicrm_financial_account.accounting_code as accounting_code,
        civicrm_note.note as contribution_note,
        civicrm_batch.title as contribution_batch,
        contribution_status.label as contribution_status,
        payment_instrument.name as contribution_payment_instrument,
        civicrm_contribution.check_number as contribution_check_number
FROM civicrm_contact contact_a
LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id 
INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id 
LEFT JOIN civicrm_entity_financial_account ON civicrm_entity_financial_account.entity_id = civicrm_contribution.financial_type_id
      AND civicrm_entity_financial_account.entity_table = 'civicrm_financial_type'
INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id
INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship
      AND cov.name = 'Income Account is'
INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
      AND cog.name = 'account_relationship'
LEFT JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id
LEFT JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id
LEFT JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_contribution'
      AND civicrm_contribution.id = civicrm_entity_batch.entity_id )
LEFT JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id
LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution'
      AND civicrm_contribution.id = civicrm_note.entity_id )
LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')
LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
      AND option_group_payment_instrument.id = payment_instrument.option_group_id )
LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')
LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
      AND option_group_contribution_status.id = contribution_status.option_group_id )
WHERE ( civicrm_contribution.is_test = 0 )
  AND (contact_a.is_deleted = 0)
GROUP BY civicrm_contribution.id
ORDER BY receive_date desc,
         contact_a.id
LIMIT 0, 10

Schema for each table involved in the query above:
-- /*******************************************************
-- *
-- * civicrm_contact
-- *
-- * Contact objects are defined by a civicrm_contact record plus a related civicrm_contact_type record.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contact` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Contact ID',
     `contact_type` varchar(64)    COMMENT 'Type of Contact.',
     `contact_sub_type` varchar(255)    COMMENT 'May be used to over-ride contact view and edit templates.',
     `do_not_email` tinyint   DEFAULT 0 ,
     `do_not_phone` tinyint   DEFAULT 0 ,
     `do_not_mail` tinyint   DEFAULT 0 ,
     `do_not_sms` tinyint   DEFAULT 0 ,
     `do_not_trade` tinyint   DEFAULT 0 ,
     `is_opt_out` tinyint NOT NULL  DEFAULT 0 COMMENT 'Has the contact opted out from receiving all bulk email from the organization or site domain?',
     `legal_identifier` varchar(32)    COMMENT 'May be used for SSN, EIN/TIN, Household ID (census) or other applicable unique legal/government ID.',
     `external_identifier` varchar(32)    COMMENT 'Unique trusted external ID (generally from a legacy app/datasource). Particularly useful for deduping operations.',
     `sort_name` varchar(128)    COMMENT 'Name used for sorting different contact types',
     `display_name` varchar(128)    COMMENT 'Formatted name representing preferred format for display/print/other output.',
     `nick_name` varchar(128)    COMMENT 'Nickname.',
     `legal_name` varchar(128)    COMMENT 'Legal Name.',
     `image_URL` varchar(255)    COMMENT 'optional URL for preferred image (photo, logo, etc.) to display for this contact.',
     `preferred_communication_method` varchar(255)    COMMENT 'What is the preferred mode of communication.',
     `preferred_language` varchar(5)    COMMENT 'Which language is preferred for communication. FK to languages in civicrm_option_value.',
     `preferred_mail_format` enum('Text', 'HTML', 'Both')   DEFAULT "Both" COMMENT 'What is the preferred mode of sending an email.',
     `hash` varchar(32)    COMMENT 'Key for validating requests related to this contact.',
     `api_key` varchar(32)    COMMENT 'API Key for validating requests related to this contact.',
     `source` varchar(255)    COMMENT 'where contact come from, e.g. import, donate module insert...',
     `first_name` varchar(64)    COMMENT 'First Name.',
     `middle_name` varchar(64)    COMMENT 'Middle Name.',
     `last_name` varchar(64)    COMMENT 'Last Name.',
     `prefix_id` int unsigned    COMMENT 'Prefix or Title for name (Ms, Mr...). FK to prefix ID',
     `suffix_id` int unsigned    COMMENT 'Suffix for name (Jr, Sr...). FK to suffix ID',
     `email_greeting_id` int unsigned    COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Email Greeting.',
     `email_greeting_custom` varchar(128)    COMMENT 'Custom Email Greeting.',
     `email_greeting_display` varchar(255)    COMMENT 'Cache Email Greeting.',
     `postal_greeting_id` int unsigned    COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Postal Greeting.',
     `postal_greeting_custom` varchar(128)    COMMENT 'Custom Postal greeting.',
     `postal_greeting_display` varchar(255)    COMMENT 'Cache Postal greeting.',
     `addressee_id` int unsigned    COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Addressee.',
     `addressee_custom` varchar(128)    COMMENT 'Custom Addressee.',
     `addressee_display` varchar(255)    COMMENT 'Cache Addressee.',
     `job_title` varchar(255)    COMMENT 'Job Title',
     `gender_id` int unsigned    COMMENT 'FK to gender ID',
     `birth_date` date    COMMENT 'Date of birth',
     `is_deceased` tinyint   DEFAULT 0 ,
     `deceased_date` date    COMMENT 'Date of deceased',
     `household_name` varchar(128)    COMMENT 'Household Name.',
     `primary_contact_id` int unsigned    COMMENT 'Optional FK to Primary Contact for this household.',
     `organization_name` varchar(128)    COMMENT 'Organization Name.',
     `sic_code` varchar(8)    COMMENT 'Standard Industry Classification Code.',
     `user_unique_id` varchar(255)    COMMENT 'the OpenID (or OpenID-style http://username.domain/) unique identifier for this contact mainly used for logging in to CiviCRM',
     `employer_id` int unsigned    COMMENT 'OPTIONAL FK to civicrm_contact record.',
     `is_deleted` tinyint NOT NULL  DEFAULT 0 ,
     `created_date` timestamp NULL  DEFAULT NULL COMMENT 'When was the contact was created.',
     `modified_date` timestamp NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the contact (or closely related entity) was created or modified or deleted.' 
,
    PRIMARY KEY ( `id` )
 
    ,     INDEX `index_contact_type`(
        contact_type
  )
  ,     INDEX `index_contact_sub_type`(
        contact_sub_type
  )
  ,     UNIQUE INDEX `UI_external_identifier`(
        external_identifier
  )
  ,     INDEX `index_sort_name`(
        sort_name
  )
  ,     INDEX `index_preferred_communication_method`(
        preferred_communication_method
  )
  ,     INDEX `index_hash`(
        hash
  )
  ,     INDEX `index_api_key`(
        api_key
  )
  ,     INDEX `index_first_name`(
        first_name
  )
  ,     INDEX `index_last_name`(
        last_name
  )
  ,     INDEX `UI_prefix`(
        prefix_id
  )
  ,     INDEX `UI_suffix`(
        suffix_id
  )
  ,     INDEX `UI_gender`(
        gender_id
  )
  ,     INDEX `index_household_name`(
        household_name
  )
  ,     INDEX `index_organization_name`(
        organization_name
  )
  ,     INDEX `index_is_deleted`(
        is_deleted
  )
  
,          CONSTRAINT FK_civicrm_contact_primary_contact_id FOREIGN KEY (`primary_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contact_employer_id FOREIGN KEY (`employer_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contribution
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution ID',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact ID',
     `financial_type_id` int unsigned    COMMENT 'FK to Financial Type for (total_amount - non_deductible_amount).',
     `contribution_page_id` int unsigned    COMMENT 'The Contribution Page which triggered this contribution',
     `payment_instrument_id` int unsigned    COMMENT 'FK to Payment Instrument',
     `receive_date` datetime    COMMENT 'when was gift received',
     `non_deductible_amount` decimal(20,2)   DEFAULT 0 COMMENT 'Portion of total amount which is NOT tax deductible. Equal to total_amount for non-deductible financial types.',
     `total_amount` decimal(20,2) NOT NULL   COMMENT 'Total amount of this contribution. Use market value for non-monetary gifts.',
     `fee_amount` decimal(20,2)    COMMENT 'actual processor fee if known - may be 0.',
     `net_amount` decimal(20,2)    COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.',
     `trxn_id` varchar(255)    COMMENT 'unique transaction id. may be processor id, bank id + trans id, or account number + check number... depending on payment_method',
     `invoice_id` varchar(255)    COMMENT 'unique invoice id, system generated or passed in',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `cancel_date` datetime    COMMENT 'when was gift cancelled',
     `cancel_reason` text    ,
     `receipt_date` datetime    COMMENT 'when (if) receipt was sent. populated automatically for online donations w/ automatic receipting',
     `thankyou_date` datetime    COMMENT 'when (if) was donor thanked',
     `source` varchar(255)    COMMENT 'Origin of this Contribution.',
     `amount_level` text    ,
     `contribution_recur_id` int unsigned    COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.',
     `honor_contact_id` int unsigned    COMMENT 'FK to contact ID',
     `is_test` tinyint   DEFAULT 0 ,
     `is_pay_later` tinyint   DEFAULT 0 ,
     `contribution_status_id` int unsigned   DEFAULT 1 ,
     `honor_type_id` int unsigned    COMMENT 'Implicit FK to civicrm_option_value.',
     `address_id` int unsigned    COMMENT 'Conditional foreign key to civicrm_address.id. We insert an address record for each contribution when we have associated billing name and address data.',
     `check_number` varchar(255)    ,
     `campaign_id` int unsigned    COMMENT 'The campaign for which this contribution has been triggered.' 
,
    PRIMARY KEY ( `id` )
 
    ,     INDEX `UI_contrib_payment_instrument_id`(
        payment_instrument_id
  )
  ,     UNIQUE INDEX `UI_contrib_trxn_id`(
        trxn_id
  )
  ,     UNIQUE INDEX `UI_contrib_invoice_id`(
        invoice_id
  )
  ,     INDEX `index_contribution_status`(
        contribution_status_id
  )
  ,     INDEX `received_date`(
        receive_date
  )
  ,     INDEX `check_number`(
        check_number
  )
  
,          CONSTRAINT FK_civicrm_contribution_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_contribution_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ,          CONSTRAINT FK_civicrm_contribution_contribution_page_id FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_contribution_recur_id FOREIGN KEY (`contribution_recur_id`) REFERENCES `civicrm_contribution_recur`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_honor_contact_id FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_address_id FOREIGN KEY (`address_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_financial_type
-- *
-- * Formerly civicrm_contribution_type merged into this table in 4.3
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'ID of original financial_type so you can search this table by the financial_type.id and then select the relevant version based on the timestamp',
     `name` varchar(64) NOT NULL   COMMENT 'Financial Type Name.',
     `description` varchar(255)    COMMENT 'Financial Type Description.',
     `is_deductible` tinyint   DEFAULT 1 COMMENT 'Is this financial type tax-deductible? If true, contributions of this type may be fully OR partially deductible - non-deductible amount is stored in the Contribution record.',
     `is_reserved` tinyint    COMMENT 'Is this a predefined system object?',
     `is_active` tinyint    COMMENT 'Is this property active?' 
,
    PRIMARY KEY ( `id` )
 
    ,     UNIQUE INDEX `UI_id`(
        id
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_entity_financial_account
-- *
-- * Map between an entity and a financial account, where there is a specific relationship between the financial account and the entity, e.g. Income Account for or AR Account for
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_financial_account` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'ID',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Links to an entity_table like civicrm_financial_type',
     `entity_id` int unsigned NOT NULL   COMMENT 'Links to an id in the entity_table, such as vid in civicrm_financial_type',
     `account_relationship` int unsigned NOT NULL   COMMENT 'FK to a new civicrm_option_value (account_relationship)',
     `financial_account_id` int unsigned NOT NULL   COMMENT 'FK to the financial_account_id' 
,
    PRIMARY KEY ( `id` )
 
 
,          CONSTRAINT FK_civicrm_entity_financial_account_financial_account_id FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account`(`id`) ON DELETE RESTRICT  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_financial_account
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_account` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'ID',
     `name` varchar(255) NOT NULL   COMMENT 'Financial Account Name.',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID that is responsible for the funds in this account',
     `financial_account_type_id` int unsigned NOT NULL  DEFAULT 3 COMMENT 'pseudo FK into civicrm_option_value.',
     `accounting_code` varchar(64)    COMMENT 'Optional value for mapping monies owed and received to accounting system codes.',
     `account_type_code` varchar(64)    COMMENT 'Optional value for mapping account types to accounting system account categories (QuickBooks Account Type Codes for example).',
     `description` varchar(255)    COMMENT 'Financial Type Description.',
     `parent_id` int unsigned    COMMENT 'Parent ID in account hierarchy',
     `is_header_account` tinyint   DEFAULT 0 COMMENT 'Is this a header account which does not allow transactions to be posted against it directly, but only to its sub-accounts?',
     `is_deductible` tinyint   DEFAULT 1 COMMENT 'Is this account tax-deductible?',
     `is_tax` tinyint   DEFAULT 0 COMMENT 'Is this account for taxes?',
     `tax_rate` decimal(10,8)    COMMENT 'The percentage of the total_amount that is due for this tax.',
     `is_reserved` tinyint    COMMENT 'Is this a predefined system object?',
     `is_active` tinyint    COMMENT 'Is this property active?',
     `is_default` tinyint    COMMENT 'Is this account the default one (or default tax one) for its financial_account_type?' 
,
    PRIMARY KEY ( `id` )
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_financial_account_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_financial_account`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_option_value
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_value` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Option ID',
     `option_group_id` int unsigned NOT NULL   COMMENT 'Group which this option belongs to.',
     `label` varchar(255) NOT NULL   COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
     `value` varchar(512) NOT NULL   COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
     `name` varchar(255)    COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.',
     `grouping` varchar(255)    COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.',
     `filter` int unsigned    COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.',
     `is_default` tinyint   DEFAULT 0 COMMENT 'Is this the default option for the group?',
     `weight` int unsigned NOT NULL   COMMENT 'Controls display sort order.',
     `description` text    COMMENT 'Optional description.',
     `is_optgroup` tinyint   DEFAULT 0 COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?',
     `is_reserved` tinyint   DEFAULT 0 COMMENT 'Is this a predefined system object?',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this option active?',
     `component_id` int unsigned    COMMENT 'Component that this option value belongs/caters to.',
     `domain_id` int unsigned    COMMENT 'Which Domain is this option value for',
     `visibility_id` int unsigned   DEFAULT NULL  
,
    PRIMARY KEY ( `id` )
 
    ,     INDEX `index_option_group_id_value`(
        value(128)
      , option_group_id
  )
  ,     INDEX `index_option_group_id_name`(
        name(128)
      , option_group_id
  )
  
,          CONSTRAINT FK_civicrm_option_value_option_group_id FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_option_value_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`) ,          CONSTRAINT FK_civicrm_option_value_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_option_group
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_group` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Option Group ID',
     `name` varchar(64) NOT NULL   COMMENT 'Option group name. Used as selection key by class properties which lookup options in civicrm_option_value.',
     `title` varchar(255)    COMMENT 'Option Group title.',
     `description` varchar(255)    COMMENT 'Option group description.',
     `is_reserved` tinyint   DEFAULT 1 COMMENT 'Is this a predefined system option group (i.e. it can not be deleted)?',
     `is_active` tinyint    COMMENT 'Is this option group active?' 
,
    PRIMARY KEY ( `id` )
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contribution_product
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_product` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `product_id` int unsigned NOT NULL   ,
     `contribution_id` int unsigned NOT NULL   ,
     `product_option` varchar(255)    COMMENT 'Option value selected if applicable - e.g. color, size etc.',
     `quantity` int    ,
     `fulfilled_date` date    COMMENT 'Optional. Can be used to record the date this product was fulfilled or shipped.',
     `start_date` date    COMMENT 'Actual start date for a time-delimited premium (subscription, service or membership)',
     `end_date` date    COMMENT 'Actual end date for a time-delimited premium (subscription, service or membership)',
     `comment` text    ,
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Financial Type(for membership price sets only).' 
,
    PRIMARY KEY ( `id` )
 
 
,          CONSTRAINT FK_civicrm_contribution_product_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_contribution_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_product
-- *
-- * able - stores "product info" for premiums and can be used for non-incentive products 
-- *
-- *******************************************************/
CREATE TABLE `civicrm_product` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `name` varchar(255) NOT NULL   COMMENT 'Required product/premium name',
     `description` text    COMMENT 'Optional description of the product/premium.',
     `sku` varchar(50)    COMMENT 'Optional product sku or code.',
     `options` text    COMMENT 'Store comma-delimited list of color, size, etc. options for the product.',
     `image` varchar(255)    COMMENT 'Full or relative URL to uploaded image - fullsize.',
     `thumbnail` varchar(255)    COMMENT 'Full or relative URL to image thumbnail.',
     `price` decimal(20,2)    COMMENT 'Sell price or market value for premiums. For tax-deductible contributions, this will be stored as non_deductible_amount in the contribution record.',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Financial Type.',
     `min_contribution` decimal(20,2)    COMMENT 'Minimum contribution required to be eligible to select this premium.',
     `cost` decimal(20,2)    COMMENT 'Actual cost of this product. Useful to determine net return from sale or using this as an incentive.',
     `is_active` tinyint NOT NULL   COMMENT 'Disabling premium removes it from the premiums_premium join table below.',
     `period_type` enum('rolling', 'fixed')   DEFAULT 'rolling' COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month
(e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) ',
     `fixed_period_start_day` int   DEFAULT 0101 COMMENT 'Month and day (MMDD) that fixed period type subscription or membership starts.',
     `duration_unit` enum('day', 'month', 'week', 'year')   DEFAULT 'year' ,
     `duration_interval` int    COMMENT 'Number of units for total duration of subscription, service, membership (e.g. 12 Months).',
     `frequency_unit` enum('day', 'month', 'week', 'year')   DEFAULT 'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.',
     `frequency_interval` int    COMMENT 'Number of units for delivery frequency of subscription, service, membership (e.g. every 3 Months).' 
,
    PRIMARY KEY ( `id` )
 
 
,          CONSTRAINT FK_civicrm_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_entity_batch
-- *
-- * Batch entities (Contributions, Participants, Contacts) to a batch.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_batch` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
     `entity_table` varchar(64)    COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
     `entity_id` int unsigned NOT NULL   COMMENT 'FK to entity table specified in entity_table column.',
     `batch_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_batch' 
,
    PRIMARY KEY ( `id` )
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  ,     UNIQUE INDEX `UI_batch_entity`(
        batch_id
      , entity_id
      , entity_table
  )
  
,          CONSTRAINT FK_civicrm_entity_batch_batch_id FOREIGN KEY (`batch_id`) REFERENCES `civicrm_batch`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_batch
-- *
-- * Stores the details of a batch operation Used primarily when doing batch operations with an external system.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_batch` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Address ID',
     `name` varchar(64)    COMMENT 'Variable name/programmatic handle for this batch.',
     `title` varchar(64)    COMMENT 'Friendly Name.',
     `description` text    COMMENT 'Description of this batch set.',
     `created_id` int unsigned    COMMENT 'FK to Contact ID',
     `created_date` datetime    COMMENT 'When was this item created',
     `modified_id` int unsigned    COMMENT 'FK to Contact ID',
     `modified_date` datetime    COMMENT 'When was this item created',
     `saved_search_id` int unsigned    COMMENT 'FK to Saved Search ID',
     `status_id` int unsigned NOT NULL   COMMENT 'fk to Batch Status options in civicrm_option_values',
     `type_id` int unsigned    COMMENT 'fk to Batch Type options in civicrm_option_values',
     `mode_id` int unsigned    COMMENT 'fk to Batch mode options in civicrm_option_values',
     `total` decimal(20,2)    COMMENT 'Total amount for this batch.',
     `item_count` int unsigned    COMMENT 'Number of items in a batch.',
     `payment_instrument_id` int unsigned    COMMENT 'fk to Payment Instrument options in civicrm_option_values',
     `exported_date` datetime     
,
    PRIMARY KEY ( `id` )
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_batch_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_batch_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_batch_saved_search_id FOREIGN KEY (`saved_search_id`) REFERENCES `civicrm_saved_search`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_note
-- *
-- * Notes can be linked to any object in the application.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_note` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Note ID',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Name of table where item being referenced is stored.',
     `entity_id` int unsigned NOT NULL   COMMENT 'Foreign key to the referenced item.',
     `note` text    COMMENT 'Note and/or Comment.',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID creator',
     `modified_date` date    COMMENT 'When was this note last modified/edited',
     `subject` varchar(255)    COMMENT 'subject of note description',
     `privacy` varchar(255)    COMMENT 'Foreign Key to Note Privacy Level (which is an option value pair and hence an implicit FK)' 
,
    PRIMARY KEY ( `id` )
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  
,          CONSTRAINT FK_civicrm_note_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_option_group
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_group` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Option Group ID',
     `name` varchar(64) NOT NULL   COMMENT 'Option group name. Used as selection key by class properties which lookup options in civicrm_option_value.',
     `title` varchar(255)    COMMENT 'Option Group title.',
     `description` varchar(255)    COMMENT 'Option group description.',
     `is_reserved` tinyint   DEFAULT 1 COMMENT 'Is this a predefined system option group (i.e. it can not be deleted)?',
     `is_active` tinyint    COMMENT 'Is this option group active?' 
,
    PRIMARY KEY ( `id` )
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_option_value
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_value` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Option ID',
     `option_group_id` int unsigned NOT NULL   COMMENT 'Group which this option belongs to.',
     `label` varchar(255) NOT NULL   COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
     `value` varchar(512) NOT NULL   COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
     `name` varchar(255)    COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.',
     `grouping` varchar(255)    COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.',
     `filter` int unsigned    COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.',
     `is_default` tinyint   DEFAULT 0 COMMENT 'Is this the default option for the group?',
     `weight` int unsigned NOT NULL   COMMENT 'Controls display sort order.',
     `description` text    COMMENT 'Optional description.',
     `is_optgroup` tinyint   DEFAULT 0 COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?',
     `is_reserved` tinyint   DEFAULT 0 COMMENT 'Is this a predefined system object?',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this option active?',
     `component_id` int unsigned    COMMENT 'Component that this option value belongs/caters to.',
     `domain_id` int unsigned    COMMENT 'Which Domain is this option value for',
     `visibility_id` int unsigned   DEFAULT NULL  
,
    PRIMARY KEY ( `id` )
 
    ,     INDEX `index_option_group_id_value`(
        value(128)
      , option_group_id
  )
  ,     INDEX `index_option_group_id_name`(
        name(128)
      , option_group_id
  )
  
,          CONSTRAINT FK_civicrm_option_value_option_group_id FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_option_value_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`) ,          CONSTRAINT FK_civicrm_option_value_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

I am thankful,

Michael Lueck
Lueck Data Systems
http://www.lueckdatasystems.com/

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Larger than expected tmp files querying InnoDB format database
1570
July 10, 2014 10:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.