13

I have the following two tables in my database (the indexing is not complete as it will be based on which engine I use):

Table 1:

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`)
)

Table 2:

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 used to create a thumbnail gallery with links to larger versions of the image. imgClass, imgFamily, and imgGender will refine the thumbnails that are shown.

Table 2 contains images related to those in Table 1. Hence the use of primaryId to relate a single image in Table 1, with one or more images in Table 2. This is where I was thinking of using the Foreign Key ability of InnoDB, but I'm also familiar with the ability of Indexes in MyISAM to do the same.

Without delving too much into the remaining fields, imgDate is used to order the results.

Last, but not least, I should mention that this database is READ ONLY. All data will be entered by me. I have been told that if a database is read only, it should be MyISAM, but I'm hoping you can shed some light on what you would do in my situation.

stefmikhail
  • 6,877
  • 13
  • 47
  • 61
  • This question begs debate ...should be migrated to Programmers ...??? – IAbstract Sep 20 '11 at 23:11
  • @IAbstract - You speak of something I'm unaware of but would be glad to oblige if you elaborate... – stefmikhail Sep 20 '11 at 23:26
  • http://Programmers.StackExchange.com is more subjective than StackOverflow. The way the question is asked will beg debate over which is better ...and, typically these questions have been closed. But since Programmers exists now and allows a greater deal of subjectivity in questions, seems like a better fit ...imho. – IAbstract Sep 21 '11 at 03:17

4 Answers4

26

Always use InnoDB by default.

In MySQL 5.1 later, you should use InnoDB. In MySQL 5.1, you should enable the InnoDB plugin. In MySQL 5.5, the InnoDB plugin is enabled by default so just use it.

The advice years ago was that MyISAM was faster in many scenarios. But that is no longer true if you use a current version of MySQL.

There may be some exotic corner cases where MyISAM performs marginally better for certain workloads (e.g. table-scans, or high-volume INSERT-only work), but the default choice should be InnoDB unless you can prove you have a case that MyISAM does better.

Advantages of InnoDB besides the support for transactions and foreign keys that is usually mentioned include:

  • InnoDB is more resistant to table corruption than MyISAM.
  • Row-level locking. In MyISAM, readers block writers and vice-versa.
  • Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
  • MyISAM is stagnant; all future development will be in InnoDB.

See also my answer to MyISAM versus InnoDB

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I will not be needing FULLTEXT indexes (as I understand them). But what do you think of what Emil Vikström stated; That I will *not* be using the benefits InnoDB affords when it comes to writing? Like I said, the queries will all be *read* queries, so would it not be faster to use MyISAM; Or does it make *that* much of a difference? – stefmikhail Sep 21 '11 at 00:10
  • Also, do you think that in my case the use of Foreign Keys would be beneficial? Or do you think that because I will be mostly reading and not writing, Foreign Keys won't make that much of a difference? Or are they even related? – stefmikhail Sep 21 '11 at 00:14
  • 1
    In my opinion, the issues of speed and foreign keys are secondary; you don't want your tables to be corrupted when your server *or* your application crashes. That rules out MyISAM. – Bill Karwin Sep 21 '11 at 00:25
  • hi @Bill Karwin "In MyISAM, readers block writers and vice-versa" - This happens because myisam uses table-level locking, that is, it locks the entire table itself, right? thanks –  Dec 21 '22 at 15:23
  • @Daniel, Correct generally, but a few edge cases exist. Like MyISAM can allow concurrent sessions to insert to the end of a table under certain conditions. – Bill Karwin Dec 21 '22 at 18:42
4

MyISAM won't enable you to do mysql level check. For instance if you want to update the imgId on both tables as 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 you can do some error check like to avoid duplicated values in the field UNIQUE KEY imgDate (imgDate). Trust me, this really come at hand and is way less error prone. In my opinion MyISAM is for playing around while some more serious work should rely on InnoDB.

Hope it helps

Chubs
  • 131
  • 1
  • 9
2

A few things to consider :

  1. Do you need transaction support?
  2. Will you be using foreign keys?
  3. Will there be a lot of writes on a table?

If answer to any of these questions is "yes", then you should definitely use InnoDB. Otherwise, you should answer the following questions :

  1. How big are your tables?
  2. How many rows do they contain?
  3. What is the load on your database engine?
  4. What kind of queries you expect to run?

Unless your tables are very large and you expect large load on your database, either one works just fine.

I would prefer MyISAM because it scales pretty well for a wide range of data-sizes and loads.

srivani
  • 994
  • 5
  • 11
  • Good to see you again, answering another of my questions. To answer yours: I need to learn more about transactional support to say if I need it or not. Care you briefly tell me what it is? I think foreign keys would be helpful to connect the tables. There will be no writes to the table, save what I will be doing to insert the data myself. Therefore write speed is not an issue. My tables will never ever be more than 1000 rows; Probably never more than 500. I will be performing several different queries. Would it be helpful to include them above? – stefmikhail Sep 21 '11 at 00:03
-1

I would like to add something that people may benefit from: I've just created a InnoDB table (leaving everything as the default, except changing the collation to Unicode), and populated it with about 300,000 records (rows).

Queries like SELECT COUNT(id) FROM table - would hang until giving an error message, not returning a result;

I've cloned the table with the data into a new MyISAM table - and that same query, along with other large SELECTqueries - would return fast, and everything worked ok.

Yuval A.
  • 5,849
  • 11
  • 51
  • 63