MySQL Forums
Forum List  »  MyISAM

Re: Using Indexes in MyISAM to Replace InnoDB Foreign Keys
Posted by: Rick James
Date: December 16, 2010 05:10PM

FOREIGN KEYS implicitly create indexes for you.

For
    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);
You need indexes for c.company_id, w.company, s.store_id, dd.date_dimension_id, p.product_id. I got that purely from how the optimizer is likely to do that query.

WHY are you moving from InnoDB to MyISAM ??? You mention faster writes -- how many writes/sec are you hoping for?
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE 'sync%';
If innodb_flush_log_at_trx_commit is 1, change to 2 and see if that will be "fast enuf".

No AUTO_INCREMENTs? Are you assigning ids yourself?

Seems like you would want an index on company_name if you every want to look up something by name. Ditto for product_name.

I would seriously consider getting rid of the table date_dimension.

Hmmm... This is amazing...
key: fk_product_company1
CONSTRAINT `fk_product_company1` FOREIGN KEY (`company`) REFERENCES `company` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
This implies that InnoDB is using the FK even when you don't explicitly specify anything in the SELECT. This implies that you would need to add to the WHERE clause!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Using Indexes in MyISAM to Replace InnoDB Foreign Keys
3426
December 16, 2010 05:10PM


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.