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

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

ただいまの
回答率

90.35%

  • MySQL

    6139questions

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

  • SQL

    2534questions

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

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 917

atLast

score 8

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

SELECT
    *
FROM
    input_irou1 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
ORDER BY
    ir1.count DESC
LIMIT 10


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

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

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

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


170128更新

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Panzer_vor

    2017/01/28 02:43

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

    キャンセル

  • atLast

    2017/01/28 16:59

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

    キャンセル

  • Panzer_vor

    2017/01/29 00:32

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

    キャンセル

回答 1

checkベストアンサー

+3

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/01/27 23:35 編集

    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

    こういうことでしょうか。今度は、結果が帰ってこなくなりました。
    出来れば実例をいただけると非常に助かります。

    キャンセル

  • 2017/01/28 00:00

    横から失礼します。

    イメージとしては恐らく合っていると思います。
    強いていうなら結合後はレコード順が保証されないので、
    もう一度ソートし直すのが無難なのですが。

    結果が返ってこないというのはSQLの応答がない or 結果が0件のどちらを指していますかね?

    キャンセル

  • 2017/01/28 00:06

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

    キャンセル

  • 2017/01/28 00:15

    > atLastさん
    ということは、
    maisumakunさんが前提条件で示されている条件を満たしていない可能性大ですね。

    input_ir1のデータは、
    input_ir2〜input_ir8全てに必ず紐付くデータが入っている訳ではないということですよね?

    キャンセル

  • 2017/01/28 01:03

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

    キャンセル

  • 2017/01/28 02:38

    > atLastさん
    一度状況を整理してみましょう。

    元々のSQLでは結果が取得可能
    → 少なくともir2〜ir8全てと紐付くデータが10件以上は存在している。

    修正後のSQLで結果が取得できない
    → 少なくとも10件以上はir1のデータで、ir2〜ir8のいずれか1つ以上と紐付かないデータが存在する。

    上記を仮説を検証するたまに、修正後のクエリからir1のみを絞りこむ箇所を抜粋し、
    上記で取得したir1.countの値がir2〜ir8まで全てに存在するかどうか確認してみてください。
    恐らくいずれかのテーブル1つ以上データが存在しないはずです。

    もし当方の読み通りであれば、maisumakunさんの示している条件2、3いずれにも違反していることになります。

    キャンセル

  • 2017/01/28 15:11

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

    キャンセル

  • 2017/01/29 00:30

    > atLastさん
    遅くなりましたが追記内容確認しました。

    今回取ってくるデータとしては、多分全カラム取得が必要なのですよね?
    そうなるとインデックスでの小細工で何とかするというのは望み薄です。(状況によればカバーリングインデックスとかもありかなと思ったのですが^^;)

    話は変わりまして、
    そもそも気になったのがテーブルを分けた理由ですかね。

    1.全テーブルで構造が同様
    2.格納されているテーブルで意味が違うだけ

    とかでしたら、
    今回のケースだと区分・種別を表すカラムを追加し、1テーブルにまとめて、
    その絡むとcountカラムの複合一主キーのテーブルとした方が高速化しそうです。

    キャンセル

  • 2017/01/29 13:28

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

    キャンセル

同じタグがついた質問を見る

  • MySQL

    6139questions

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

  • SQL

    2534questions

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