カテゴリに所属するアイテムの表示・非表示(MySQLの実装失敗パターン)

仕様

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

失敗実装パターン

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

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

以上。