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

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

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

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

1回答

4448閲覧

SQLで約10万件のデータが入っているテーブルのjoinについて

atLast

総合スコア14

MySQL

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

1クリップ

投稿2017/01/27 13:17

編集2017/01/28 07:03

現在、テーブル8個に分割したデータを一つにまとめようとしています。
各テーブルは、約10万件のデータが入っています。
このデータをプライマリキーでjoinして取得しようとして、下記のクエリを書きました。

SQL

1SELECT 2 * 3FROM 4 input_irou1 AS ir1 5JOIN input_ir2 AS ir2 ON ir1.count = ir2.count 6JOIN input_ir3 AS ir3 ON ir1.count = ir3.count 7JOIN input_ir4 AS ir4 ON ir1.count = ir4.count 8JOIN input_ir5 AS ir5 ON ir1.count = ir5.count 9JOIN input_ir6 AS ir6 ON ir1.count = ir6.count 10JOIN input_ir7 AS ir7 ON ir1.count = ir7.count 11JOIN input_ir8 AS ir8 ON ir1.count = ir8.count 12ORDER BY 13 ir1.count DESC 14LIMIT 10

上記クエリでjoinできるのはわかるんですが、explainをかけると
いずれかのテーブルで、using temporaryとusing filesortが出現してしまいます。

各テーブルのcountにはprimaryがついています。

using temporary, using filesortを出さずにjoinする方法はないでしょうか。
速度が非常に遅く、困っております。

宜しくお願いいたします。


170128更新

テーブル構造は以下の形です。
(input_irou1の例ですが、2~8もほぼ同様です)
テーブル定義
※1全テーブルでインデックス定義はありません
※2鍵マークはprimaryの定義のいみです。

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

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

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

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

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

Panzer_vor

2017/01/27 14:28

ORDER BY句を外した場合も同じく遅い感じでしょうか?
atLast

2017/01/27 14:39

コメントありがとうございます。ORDER BY句を外しても、Explainの結果及び実際のクエリ時間は殆ど変わりませんでした。
Panzer_vor

2017/01/27 14:48

ソートに時間が取られてる線はなさそうですね。maisumakunさんの回答出てますが、速度面でいうと先にメインテーブルで10件に絞ってから他のテーブルをくっつけるのが有効かもしれませんね。
Panzer_vor

2017/01/27 17:42

場合によってはインデックスの貼り方で速度改善が図れるケースもあるので、構造(カラム名はダミーでもいいです)を掲示することはできますか?
Panzer_vor

2017/01/27 17:43

スマホからでタイプミスしました。上でいう構造というのはテーブル定義のことです^^;
atLast

2017/01/28 07:59

外部ツールを使ってのテーブル定義を貼り付けました。参考までに。(足りない情報がある場合は教えて下さい
Panzer_vor

2017/01/28 15:32

内容確認しました。また、コメントを受けて返答してますので確認してみて下さい。
guest

回答1

0

ベストアンサー

すべてがINNER JOINである以上、JOINしてみなければどこまでがLIMIT 10の範囲かわからないため、全件でJOINを始めてしまいます。

以下の3条件のどれかを満たす場合、先にinput_irou1から10個引いた上で、そこに残りのテーブルをJOINさせる、という手段が考えられます。

  • あとの処理上、input_irou1に対するLEFT JOINで問題ない
  • どのレコードについてもテーブル8つは必ず揃っている
  • 8つのテーブルのどれかが欠けることが稀で、かつ1つぐらい減っても問題はない

投稿2017/01/27 14:00

maisumakun

総合スコア145121

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

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

atLast

2017/01/27 14:38 編集

SELECT   * FROM   (     SELECT * FROM input_irou1 ORDER BY count DESC LIMIT 10   ) AS ir1 JOIN input_ir2 AS ir2 ON ir1.count = ir2.count JOIN input_ir3 AS ir3 ON ir1.count = ir3.count JOIN input_ir4 AS ir4 ON ir1.count = ir4.count JOIN input_ir5 AS ir5 ON ir1.count = ir5.count JOIN input_ir6 AS ir6 ON ir1.count = ir6.count JOIN input_ir7 AS ir7 ON ir1.count = ir7.count JOIN input_ir8 AS ir8 ON ir1.count = ir8.count こういうことでしょうか。今度は、結果が帰ってこなくなりました。 出来れば実例をいただけると非常に助かります。
Panzer_vor

2017/01/27 15:00

横から失礼します。 イメージとしては恐らく合っていると思います。 強いていうなら結合後はレコード順が保証されないので、 もう一度ソートし直すのが無難なのですが。 結果が返ってこないというのはSQLの応答がない or 結果が0件のどちらを指していますかね?
atLast

2017/01/27 15:06

返答ありがとうございます。結果が0件です。
Panzer_vor

2017/01/27 15:15

> atLastさん ということは、 maisumakunさんが前提条件で示されている条件を満たしていない可能性大ですね。 input_ir1のデータは、 input_ir2〜input_ir8全てに必ず紐付くデータが入っている訳ではないということですよね?
atLast

2017/01/27 16:03

>Panzer_vorさん あくまでツールを使った目視とSQLでの確認ですが、紐付くデータは入っています。 一番最初のinner join句のSQLだと、値が取れることを確認しています。
Panzer_vor

2017/01/27 17:38

> atLastさん 一度状況を整理してみましょう。 元々のSQLでは結果が取得可能 → 少なくともir2〜ir8全てと紐付くデータが10件以上は存在している。 修正後のSQLで結果が取得できない → 少なくとも10件以上はir1のデータで、ir2〜ir8のいずれか1つ以上と紐付かないデータが存在する。 上記を仮説を検証するたまに、修正後のクエリからir1のみを絞りこむ箇所を抜粋し、 上記で取得したir1.countの値がir2〜ir8まで全てに存在するかどうか確認してみてください。 恐らくいずれかのテーブル1つ以上データが存在しないはずです。 もし当方の読み通りであれば、maisumakunさんの示している条件2、3いずれにも違反していることになります。
atLast

2017/01/28 06:11

大変失礼しました。irou1のみデータがあり、2~8にデータが無い状態を確認しました。 where句で、存在するデータを絞り込んでみたところ、約5秒かかりました。 これ以上の短縮は不可能でしょうか。。
Panzer_vor

2017/01/28 15:30

> atLastさん 遅くなりましたが追記内容確認しました。 今回取ってくるデータとしては、多分全カラム取得が必要なのですよね? そうなるとインデックスでの小細工で何とかするというのは望み薄です。(状況によればカバーリングインデックスとかもありかなと思ったのですが^^;) 話は変わりまして、 そもそも気になったのがテーブルを分けた理由ですかね。 1.全テーブルで構造が同様 2.格納されているテーブルで意味が違うだけ とかでしたら、 今回のケースだと区分・種別を表すカラムを追加し、1テーブルにまとめて、 その絡むとcountカラムの複合一主キーのテーブルとした方が高速化しそうです。
atLast

2017/01/29 04:28

>Panzer_vorさん 高速化の案ありがとうございます。 既に作ってしまった仕組みなので、今から変えるのは難しいですが、今後設計するときの参考にさせていただきます。 重ね重ね、ありがとうございました!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問