Using Indexes in MyISAM to Replace InnoDB Foreign Keys

[Michael Palmer]

I am posting because I am having problems understanding how best to replace foreign keys in InnoDB with Indexes in MyISAM.

We are exploring the possibility of separating our transactional (OLTP) and non-transactional(OLAP) databases to increase business process efficiency. One of our databases (dw) is currently set up to use InnoDB and includes these tables:

=============== START Orginal InnoDB Tables ==================

TABLE `weekly_sales_fact` (
`sales_week` int(10) NOT NULL,
`company` int(10) unsigned NOT NULL,
`product` int(10) unsigned NOT NULL,
`store` int(10) unsigned NOT NULL,
`units_sold` int(10) NOT NULL,
`dollars_sold` double NOT NULL,
PRIMARY KEY (`sales_week`,`company`,`product`,`store`),
KEY `fk_weekly_sales_fact_date_dimension1` (`sales_week`),
KEY `fk_weekly_sales_fact_store1` (`store`),
KEY `fk_weekly_sales_fact_company1` (`company`),
KEY `fk_weekly_sales_fact_product1` (`product`),
CONSTRAINT `fk_weekly_sales_fact_date_dimension1` FOREIGN KEY (`sales_week`) REFERENCES `date_dimension` (`date_dimension_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_weekly_sales_fact_store1` FOREIGN KEY (`store`) REFERENCES `store` (`store_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_weekly_sales_fact_company1` FOREIGN KEY (`company`) REFERENCES `company` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_weekly_sales_fact_product1` FOREIGN KEY (`product`) REFERENCES `product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

TABLE `product` (
`product_id` int(10) unsigned NOT NULL,
`product_name` varchar(100) NOT NULL,
`company` int(10) unsigned NOT NULL,
PRIMARY KEY (`product_id`) USING BTREE,
KEY `fk_product_company1` (`company`),
CONSTRAINT `fk_product_company1` FOREIGN KEY (`company`) REFERENCES `company` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

TABLE `date_dimension` (
`date_dimension_id` int(11) NOT NULL,
`dt` date NOT NULL,
`date_dimension_id_preceding_period` int(11) DEFAULT NULL,
`date_dimension_id_year_ago_period` int(11) DEFAULT NULL,
PRIMARY KEY (`date_dimension_id`) USING BTREE,
KEY `fk_date_dimension_preceding_period1` (`date_dimension_id_preceding_period`),
KEY `fk_date_dimension_year_ago_period1` (`date_dimension_id_year_ago_period`),
CONSTRAINT `fk_date_dimension_preceding_period1` FOREIGN KEY (`date_dimension_id_preceding_period`) REFERENCES `date_dimension` (`date_dimension_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_date_dimension_year_ago_period1` FOREIGN KEY (`date_dimension_id_year_ago_period`) REFERENCES `date_dimension` (`date_dimension_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

TABLE `store` (
`store_id` int(10) unsigned NOT NULL,
`store_name` varchar(45) DEFAULT NULL,
`street_address_1` varchar(75) DEFAULT NULL,
`street_address_2` varchar(75) DEFAULT NULL,
`city` varchar(45) DEFAULT NULL,
`state` varchar(45) DEFAULT NULL,
`zip` varchar(45) DEFAULT NULL,
`country` varchar(45) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
`fax` varchar(45) DEFAULT NULL,
`region` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`store_id`) USING BTREE,
KEY `fk_store_region1` (`region`),
CONSTRAINT `fk_store_region1` FOREIGN KEY (`region`) REFERENCES `region` (`region_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

TABLE `company` (
`company_id` int(10) unsigned NOT NULL,
`company_name` varchar(45) NOT NULL,
PRIMARY KEY (`company_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
=============== END Orginal Tables ======================

As an experiment I have converted the database to use MyISAM.

Since MyISAM does not support foreign, I converted these foreign keys to indexes and removed the constraints:

=============== START Tables Converted to MyISAM ========
TABLE `weekly_sales_fact` (
`sales_week` int(10) NOT NULL,
`company` int(10) unsigned NOT NULL,
`product` int(10) unsigned NOT NULL,
`store` int(10) unsigned NOT NULL,
`units_sold` int(10) NOT NULL,
`dollars_sold` double NOT NULL,
PRIMARY KEY (`sales_week`,`company`,`product`,`store`),
KEY `sales_week` (`sales_week`),
KEY `company` (`company`),
KEY `product` (`product`),
KEY `store` (`store`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

TABLE `product` (
`product_id` int(10) unsigned NOT NULL,
`product_name` varchar(100) NOT NULL,
`company` int(10) unsigned NOT NULL,
PRIMARY KEY (`product_id`) USING BTREE,
KEY `company` (`company`) USING BTREE,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

TABLE `date_dimension` (
`date_dimension_id` int(11) NOT NULL,
`dt` date NOT NULL,
`date_dimension_id_preceding_period` int(11) DEFAULT NULL,
`date_dimension_id_year_ago_period` int(11) DEFAULT NULL,
PRIMARY KEY (`date_dimension_id`) USING BTREE,
KEY `date_dimension_id_preceding_period` (`date_dimension_id_preceding_period`) USING BTREE,
KEY `date_dimension_id_year_ago_period` (`date_dimension_id_year_ago_period`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

TABLE `store` (
`store_id` int(10) unsigned NOT NULL,
`store_name` varchar(45) DEFAULT NULL,
`street_address_1` varchar(75) DEFAULT NULL,
`street_address_2` varchar(75) DEFAULT NULL,
`city` varchar(45) DEFAULT NULL,
`state` varchar(45) DEFAULT NULL,
`zip` varchar(45) DEFAULT NULL,
`country` varchar(45) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
`fax` varchar(45) DEFAULT NULL,
`region` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`store_id`) USING BTREE,
KEY `region` (`region`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

TABLE `company` (
`company_id` int(10) unsigned NOT NULL,
`company_name` varchar(45) NOT NULL,
PRIMARY KEY (`company_id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

=============== END Tables Converted to MyISAM ==========

============== Test Query ===============================
EXPLAIN
SELECT
# This query pulls the sales from the last four weeks
c.display_name AS "Company Name",
SUM(w.units_sold*w.avg_retail_price) AS "$ Sales",
SUM(w.units_sold) AS "Unit Sales"
FROM
weekly_sales_fact w
INNER JOIN product p ON (w.product=p.product_id)
INNER JOIN date_dimension dd ON w.sales_week=dd.date_dimension_id
INNER JOIN store s ON w.store=s.store_id
INNER JOIN company c ON w.company=c.company_id
WHERE
c.company_id IN (151);
============== END Test Query ===========================

When I run this query against the original InnoDB database:

I get this information:

--------------------------------
id: 1
select_type: SIMPLE
table: c
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:

--------------------------------
id: 1
select_type: SIMPLE
table: p
type: index
possible_keys: PRIMARY
key: fk_product_company1
key_len: 4
ref: 4
rows: 22
Extra: Using index

--------------------------------
id: 1
select_type: SIMPLE
table: dd
type: index
possible_keys: PRIMARY
key: fk_date_dimension_preceding_period1
key_len: 5
ref: NULL
rows: 4041
Extra: Using index; Using join buffer

--------------------------------
id: 1
select_type: SIMPLE
table: w
type: ref
possible_keys: PRIMARY,fk_weekly_sales_fact_date_dimension1,fk_weekly_sales_fact_store1,fk_weekly_sales_fact_company1,fk_weekly_sales_fact_product1
key: PRIMARY
key_len: 12
ref: dw.dd.date_dimension_id,const,dw.p.product_id
rows: 21
Extra:

--------------------------------
id: 1
select_type: SIMPLE
table: s
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dw.w.store
rows: 1
Extra: Using index
-------------------------------
===============================

But when I run the same query against the new experimental MyISAM database, I get a row scan on the weekly_sales_fact table (w):

===============================
--------------------------------
id: 1
select_type: SIMPLE
table: c
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:

--------------------------------
id: 1
select_type: SIMPLE
table: w
type: ALL
possible_keys: PRIMARY,sales_week,company,product,store
key: NULL
key_len: NULL
ref: NULL
rows: 4521653
Extra: Using where

--------------------------------
id: 1
select_type: SIMPLE
table: s
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dw_exp.w.store
rows: 1
Extra: Using index

--------------------------------
id: 1
select_type: SIMPLE
table: dd
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dw_exp.w.sales_week
rows: 1
Extra: Using index

--------------------------------
id: 1
select_type: SIMPLE
table: p
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dw_exp.w.product
rows: 1
Extra: Using index
=========================

The experimental MyISAM database seems to be allowing for faster writes but I am still trying replace the foreign keys with proper indexes.

Is there a way to construct the weekly_sales_fact table to make avoid the row scan that has resulted from the deletion of the Foreign Keys and Constraints? Could the table's complex primary key be changed to help allow the query to run without the row scan?

Or is my test query just poorly written?

Thank you for any advice or pointers,

~Mike








------