Which engine to choose in MySQL

Question

I am designing online shop database in MySQL, below is my code:

CREATE  TABLE IF NOT EXISTS `eshop`.`sellers` (
  `seller_id` INT(11) NOT NULL ,
  `seller_name` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `contacts` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`seller_id`) ,
  UNIQUE INDEX `seller_id_UNIQUE` (`seller_id` ASC) ,
  UNIQUE INDEX `seller_name_UNIQUE` (`seller_name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8

CREATE  TABLE IF NOT EXISTS `eshop`.`goods` (
  `good_id` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `description` TEXT NULL ,
  `price` INT NULL ,
  `photo` VARCHAR(45) NOT NULL ,
  `owner_name` VARCHAR(45) NULL ,
  `new` TINYINT(1) NOT NULL ,
  `approvement_status` TINYINT NOT NULL ,
  `contacts` VARCHAR(150) NOT NULL ,
  `date` DATE NOT NULL ,
  `seller_id_fk` INT NOT NULL ,
  PRIMARY KEY (`good_id`) ,
  UNIQUE INDEX `good_id_UNIQUE` (`good_id` ASC) ,
  INDEX `seller_id_fk` (`seller_id_fk` ASC) ,
  CONSTRAINT `seller_id_fk`
    FOREIGN KEY (`seller_id_fk` )
    REFERENCES `eshop`.`sellers` (`seller_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8

I have problem between choosing MySQL engine, from this post I understood the difference between MyISAM and InnoDB. In my project there will be more reads than writes as shop goods will be viewed more than added, so I need MyISAM, but in the above script I have used InnoDB because it supports Foreign Key. What should I choose?

My personal opinion is that you should just stick with InnoDB for everything unless you have a really big data set and specific needs that, after benchmarking, show that MyISAM is more performant and that you can do without integrity constraints. — siride, Dec 01 '12 at 17:15

score 1 · Answer 1 · answered Dec 01 '12 at 17:17

The question you are asking is about optimization and performance. If you're lucky enough that your stock and your user base become very large, then you can worry about tweaking things so you have lots of performance. You may be able also to take advantage of future advances in the MySQL database engine technology. What you have will work fine for hundreds of thousands of users and tens of thousands of items for sale.

For now, don't waste time worrying about such things. Use what you have, complete the development of your project, and get it out before the public.

score 0 · Answer 2 · answered Dec 01 '12 at 17:21

0

If you use MyISAM, your application will have to maintain the referential integrity on it's own, so I would base the decision on the design of the application's framework.

answered Dec 01 '12 at 17:21

hoeni

3,031
30
45

Which engine to choose in MySQL

2 Answers2