Re: Using Indexes in MyISAM to Replace InnoDB Foreign Keys

[Michael Palmer]

Rick, thank you for taking the time to respond to my question.

First, in response to your question about why we are considering moving away form InnoDB, is that we are not using this database in a transactional way. Several times a week, data is written into this particular database, and at other times the data queried by other applications, mainly for reporting purposes. Because of this, I was investigating if eliminating the InnoDB's row/cell level locking would improve the performance when we are doing large data writes or processing complex queries.

Thank you for the pointers on the indexing, I will add indexes on c.company_id, w.company, s.store_id, dd.date_dimension_id, p.product_id.

Finally, I could better leverage the fk_product_company1 by adding a seconde condition to my query like this below:

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)
AND p.product_name = "Apples";


Thank you again for your time and your advice,

Mike