質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

Q&A

解決済

1回答

967閲覧

条件変更により様々なクエリを生成して検索実行する場合を想定したテーブルのインデックス作成について

yuki84web

総合スコア1857

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

0グッド

0クリップ

投稿2020/07/25 10:43

編集2020/07/25 17:12

前提

検索条件を細かく設定し、その条件に合致する結果を一覧で表示出来る画面があります。

検索対象となるのは、例としてイベントへの応募データを管理する下記のようなテーブル(enters)です。

CREATE TABLE `enters` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `event_id` BIGINT UNSIGNED NOT NULL, FOREIGN KEY (event_id) REFERENCES events(id), `name` varchar(255), `status` TINYINT UNSIGNED NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `edited_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE `events` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255), (省略) );

このテーブルの更新対象カラムはstatusとedited_atのみ。
event_idでイベントテーブル(events)と結合し、イベント名(varchar型)を取得します。

画面初期表示時は、自動で下記クエリを発行します。

SELECT * FROM enters INNER JOIN events ON enters.event_id = events.id ORDER BY created_at DESC LIMIT 100

検索実行時は、下記それぞれを任意で指定し、それを元にクエリを生成して発行します。

  • イベント(event_id)
  • 応募状況ステータス(status)
  • 対象日時(created_at) ※開始と終了を両方または片方のみ指定可能
  • 取得順序 ※降順(デフォルト)or昇順
  • 取得件数 ※指定件数(デフォルト、上限1000とする)or全件

ただし、取得件数が全件の場合は、イベント/ステータス/対象日付いずれかを指定する必要があるものとします。

発行されるクエリ例
※"SELECT * FROM enters INNER JOIN events ON enters.event_id = events.id"は固定なので[SELECT...]として省略します

[SELECT...] ORDER BY created_at ASC LIMIT 1000 [SELECT...] WHERE event_id = 1 ORDER BY created_at DESC [SELECT...] WHERE event_id = 1 AND status = 0 ORDER BY created_at ASC LIMIT 1000 [SELECT...] WHERE event_id = 1 AND status = 0 AND created_at >= '2020-01-01 00:00:00' [SELECT...] WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' ORDER BY created_at DESC

実現したいこと

上記を踏まえ、まず最初の画面初期表示時に発行されるクエリの為に、"created_at"カラムにインデックスが必要だとは思うのですが、
それ以外のカラムについては"発行されるクエリ例"のようにクエリのパターンが多く、どのようにすべきか悩んでいます。
あらゆるパターンを考慮して複合インデックス含め作成しておくべきでしょうか。

なお、現状のレコード数は1000件にも満たないので、インデックスの有効性は無いと思っていますが、
一切レコードを物理削除することなく運用を続けていき、いずれチューニングを無視できないレコード数になった場合を想定して検討中です。

補足情報

MySQL 5.7

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yuki84web

2020/07/25 14:09

実際は、フレームワークのマイグレーションファイルによって生成したのでCREATE TABLE文が無いのですが、用意出来次第編集追記しておきます。
yuki84web

2020/07/25 14:36

ありがとうございます。現時点で手元に環境が無いので、後程対応しておきます。
guest

回答1

0

ベストアンサー

[SELECT...] WEHER event_id = 1 AND status = 0 AND created_at >= '2020-01-01 00:00:00'

event_id, status, created_at でインデックスを1つ。

[SELECT...] WEHER created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' ORDER BY created_at DESC

created_at でインデックスを1つ。

それ以外のカラムについてはクエリのパターンが多く、

そのパターンも質問に追記しては?
めったにSELECTされない条件ならインデックスは作らないとか。

MySQL SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう
を確認したり、処理時間を実測してインデックスを設定します。

投稿2020/07/25 12:53

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yuki84web

2020/07/25 14:05

回答ありがとうございます。「そのパターンも質問に追記しては?」については"発行されるクエリ例"で掲載したものがそれにあたります。とりあえず、ご提示頂いたカラムにインデックスを作成し、EXPLAINで結果を確認して対応を検討します。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問