MyISAM, InnoDB and Foreign Keys

[Voiper99]

Hi all, I'd like to create a new table to store information for my plugin. The information will be related to the hosts already created in Cacti, so the best way I can think of this is to create an InnoDB table and use Foreign Keys to reference the data in the 'host' table, however, the 'host' table is a MyISAM, and that leads me to two questions:

1. Can I even do this (use a MyISAM primary key to act as a foreign key for InnoDB)?
2. I have noticed that all of the tables in my installation of Cacti is MyISAM. Is there any reason for this apart from MyISAM being the fastest type to retreive information from?

[TheWitness]

Cacti's real old and so is MyISAM. Innodb complicates things for a system whose users are not too complicated. On the key issue, use the "explain select" mysql command to see if your joins are optimized.

TheWitness

[raine]

InnoDB store engine scales a lot better than deprecated MyISAM.

See: http://blogs.oracle.com/mysql/2011/01/c ... mance.html

[Howie]

Also, the 'foreign keys' in the Cacti schema are pretty wacky - often back to the same table for things like templates.

[TheWitness]

I now use memory, myisam, and innodb.. and both adhoc and builtin partitioning.

They each have their strong points.

[gandalf]

Also, the 'foreign keys' in the Cacti schema are pretty wacky - often back to the same table for things like templates.

Yep, ugly. Any ideas for a better table layout?
R.

[denstock]

Also, the 'foreign keys' in the Cacti schema are pretty wacky - often back to the same table for things like templates.

seems to be the only way...