I've the next two tables during my database (the indexing is not complete as it will likely be according to which engine I personally use):
CREATE TABLE `primary_images` ( `imgId` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `imgTitle` varchar(255) DEFAULT NULL, `view` varchar(45) DEFAULT NULL, `secondary` enum('true','false') NOT NULL DEFAULT 'false', `imgURL` varchar(255) DEFAULT NULL, `imgWidth` smallint(6) DEFAULT NULL, `imgHeight` smallint(6) DEFAULT NULL, `imgDate` datetime DEFAULT NULL, `imgClass` enum('jeans','t-shirts','shoes','dress_shirts') DEFAULT NULL, `imgFamily` enum('boss','lacoste','tr') DEFAULT NULL, `imgGender` enum('mens','womens') NOT NULL DEFAULT 'mens', PRIMARY KEY (`imgId`), UNIQUE KEY `imgDate` (`imgDate`) )
CREATE TABLE `secondary_images` ( `imgId` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `primaryId` smallint(6) unsigned DEFAULT NULL, `view` varchar(45) DEFAULT NULL, `imgURL` varchar(255) DEFAULT NULL, `imgWidth` smallint(6) DEFAULT NULL, `imgHeight` smallint(6) DEFAULT NULL, `imgDate` datetime DEFAULT NULL, PRIMARY KEY (`imgId`), UNIQUE KEY `imgDate` (`imgDate`) )
Table 1 will be employed to produce a thumbnail gallery with links to bigger versions from the image.
imgGender will refine the pictures which are proven.
Table 2 consists of images related to individuals in Table 1. Hence using
primaryId to relate just one image in Table 1, with a number of images in Table 2. This is when I believed of utilizing the Foreign Key ability of InnoDB, but I am also acquainted with ale Indexes in MyISAM to complete exactly the same.
Without diving an excessive amount of in to the remaining fields,
imgDate can be used to buy the outcomes.
Last, although not least, I ought to point out that this database is READ ONLY. All data is going to be joined by me. I've been told when a database is read only, it ought to be MyISAM, but I am wishing you are able to shed some light on which you would do during my situation.
In modern versions of MySQL, that's 5.1 and 5.5, you need to use InnoDB. In MySQL 5.1, you need to enable the InnoDB plugin. In MySQL 5.5, the InnoDB wordpress plugin is enabled automatically so only use it.
The recommendation years back was that MyISAM was faster in lots of situations. But that's no more true if you are using a present version of MySQL.
There might be some exotic corner cases when MyISAM works marginally better for several workloads, however the default choice ought to be InnoDB.
Benefits of InnoDB aside from the support for transactions and foreign secrets that's usually pointed out include:
- InnoDB is much more resistant against table corruption than MyISAM.
- Row-level securing. In MyISAM, visitors block authors and vice-versa.
- Support for big buffer pool for data and indexes. MyISAM key buffer is just for indexes.
- MyISAM is stagnant all future improvements come in InnoDB.
Virtually the only real reason to make use of MyISAM in current versions is perfect for FULLTEXT indexes. And that is because of be supported in InnoDB in MySQL 5.6.
MyISAM will not allow you to do mysql level check. For example if you wish to update the imgId on tables like a single transaction:
START TRANSACTION; UPDATE primary_images SET imgId=2 WHERE imgId=1; UPDATE secondary_images SET imgId=2 WHERE imgId=1; COMMIT;
Another drawback is integrity check, using InnoDB that you can do some error check prefer to avoid copied values within the area UNIQUE KEY
imgDate). Believe me, this really come at hands and it is way less error prone. For me MyISAM is perfect for experimenting while more serious work should depend on InnoDB.
Hope it will help
MyISAM is usually faster for reads (especially if you possess the table in fixed-row format, with CHAR rather than VARCHAR), however the database integrity and gratifaction isn't good when you are performing creates from multiple locations at the same time. This is exactly why many people prefer using InnoDB, which assists concurrent processing and securing far better.
You are clearly not impacted by the writing cons, since youäre not doing lots of creates, so that you can opt for MyISAM.