blog.jiikko.com

仕様

• アイテムは1つのカテゴリに所属する
• カテゴリに所属しないアイテムを作成できる(非表示状態)

失敗実装パターン

itemsの category_idにnullが入ると「カテゴリに無所属・非表示になる」という実装は失敗パターンです。

CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_items_on_category_id` (`category_id`),
)

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
)

なぜこの実装が失敗なのかというと、カテゴリに所属しているだけのアイテムを検索すると、インデックスの使えないnot nullを使ったSQLになるからです。

• 両方
  • select * from items
• カテゴリに所属している
  • select * from items where category_id not null
• カテゴリに所属していない(非表示)
  • select * from items where category_id is null

よい実装

可視性を表現するためのカラム( visibility)を追加し、
先頭にvisibilityを持つマルチカラムインデックスを作成します。 visibilityを先頭にもつことで、表示・非表示の検索で常にインデックスを使った検索ができます。

• 両方
  • select * from items where visibility in (1, 0)
• カテゴリに所属している
  • select * from items where visibility in (1)
• カテゴリに所属していない(非表示)
  • select * from items where visibility in (0)

CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `visibility` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_items_on_visibility_and_category_id` (`visibility`, `category_id`),
)

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
)

以上。