From: Batara Kesuma (bkesumaml.gaijinweb.com)
Date: Thu Jul 01 2004 - 21:08:42 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi,

I read it somewhere that InnoDB is faster for table with high read/write
concurrency. I have a table look like this:

CREATE TABLE diary (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  date DATE NOT NULL,
  time TIME NOT NULL,
  last_accessed TIMESTAMP
  PRIMARY KEY (id),
  INDEX member_id (member_id)
) TYPE=InnoDB;

INSERT only occurs when someone writes a new diary, which is not very
often. But UPDATE occurs everytime diary is accessed, so it happens
often.

I am thinking to divide the table into MySQL and InnoDB like this:

CREATE TABLE diary (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  date DATE NOT NULL,
  time TIME NOT NULL,
  PRIMARY KEY (id),
  INDEX member_id (member_id)
) TYPE=MyISAM;

CREATE TABLE diary_info (
  diary_id INT UNSIGNED NOT NULL,
  last_accessed TIMESTAMP,
  PRIMARY KEY(diary_id)
) TYPE=InnoDB;

So it is only diary_info that has high read/write concurrency now. But,
everytime I have to do a SELECT, I also have to JOIN diary_info. Which
one do you think is better?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]