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

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

ただいまの
回答率

88.33%

PHPでデータベースからデータを取得する際、とても時間がかかってしまうのを改善したい

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 733

bws

score 76

やりたいこと

下記テーブルから商品の
sum(sales_products.quantity)(注文数合計)
categories.taxonomy_id(カテゴリー分類ID)
sales.office_id AS office_id(販売事業所ID)

categories.taxonomy_id(カテゴリー分類)
sales.office_id AS office_id(販売事業所ID)
でグループ化して取得したい。

困っていること

salesテーブル => orderテーブルevent_salesテーブルevent_orderテーブル
sales_productsテーブル => order_productsテーブル、 event_sales_productsテーブルevent_order_productsテーブル

といったように似たテーブルが他に3種類あり、
下記コードのように合計で3回データベースから取得するのでかなりの時間がかかってしまいます。

少しでもパフォーマンスをよくしたいのですが、どのようにしたらよいでしょうか?
アドバイスお願いします。

テーブル

sales(注文)テーブル (5万くらい)

id shop_id office_id date
1 1 1 0000-00-00
2 1 2 0000-00-00
3 1 2 0000-00-00
// CREATE
CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shop_id` int(11) NOT NULL,
  `office_id` int(11) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53158 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

sales_products(注文商品)テーブル(300万くらい)

id sales_id product_id quantity
1 1 1 5
2 1 3 7
3 1 2 7
// CREATE
CREATE TABLE `sales_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sales_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2504749 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

products(商品)テーブル(3万くらい)

id category_id name
1 1 商品1
1 商品2
1 商品3
// CREATE
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28449 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

// INSERT
$sql = "INSERT INTO products (category_id, name) VALUES (:category_id, :name)";

categories(商品カテゴリー)テーブル(200くらい)

id taxonomy_id name
1 1 カテゴリー1
2 1 カテゴリー2
3 1 カテゴリー3
// CREATECREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `taxonomy_id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=174 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

コード

$sql =
"SELECT
  sum(sales_products.quantity) AS sum_quantity,
  categories.taxonomy_id AS taxonomy,
  sales.office_id AS office_id
FROM sales, sales_products, products, categories
WHERE sales.id = sales_product.sales_id
AND sales_products.product_id = products.id
AND product.category_id = categories.id
AND (sales.date BETWEEN :from AND :to)
GROUP BY categories.taxonomy_id, sales.office_id";

$stmt = $this->dbh->prepare($sql);
$data = array(
  ':from' => $from,
  ':to' => $to
);
$stmt->execute($data);
$rec = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 他テーブルも以下のように取得
$sql =
"SELECT
  sum(order_products.quantity) AS sum_quantity,
  categories.taxonomy_id AS taxonomy,
  order.office_id AS office_id
FROM order, order_products, products, categories
WHERE order.id = order_product.sales_id
AND order_products.product_id = products.id
AND product.category_id = categories.id
AND (order.date BETWEEN :from AND :to)
GROUP BY categories.taxonomy_id, order.office_id";

$stmt = $this->dbh->prepare($sql);
$data = array(
  ':from' => $from,
  ':to' => $to
);
$stmt->execute($data);
$rec = $stmt->fetchAll(PDO::FETCH_ASSOC);
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • m.ts10806

    2019/08/15 17:54

    テーブル定義(CREATE TABLE文)とサンプルデータのINSERT文をご提示ください。
    (table組で提示されているところを差し替えてもらったほうがいいですね)
    また、「とても時間がかかる」というのはどれくらいでしょうか。
    それぞれのデータ件数もご提示ください。

    キャンセル

  • m.ts10806

    2019/08/15 18:06

    CREATE TABLE文 と サンプルデータのINSERT文 です。
    事情があって提示できないのでしたらそう書いていただけたらと。
    要は実行するだけで同じ環境が作れるSQLを提示してほしいのです。
    手元で再現確認もしたいですしね。

    キャンセル

  • bws

    2019/08/16 09:51

    立て込んでしまい中途半端の追記になってしまって申し訳ありません。
    CREATE文追記させていただきました。sqlのバックアップを仮想環境でそのまま使っている為、サンプルデータを使用していません。よろしくお願いします。

    キャンセル

回答 3

+3

JOINしてから注文日で絞り込んでいるので、この時点で組み合わせが膨大になっている気がするので
先に注文日で絞り込んだorderをサブクエリにしたらどうでしょうか?

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/08/15 21:11

    あと内容的に、カテゴリごとの販売ランキングっぽいので、定期バッチにしてn時間おきにみれます
    みたいな運用にするのが簡単そうな気がします

    キャンセル

  • 2019/08/16 09:58

    ありがとうございます!
    サブクエリを使ったことがありませんでした、このような時に使うんですね!試してみます!
    販売ランキング表示用のテーブルを作って定期的にINSERTしていく。そのテーブルから読み込むという認識で大丈夫そうでしょうか?

    キャンセル

checkベストアンサー

+2

sales の shop_id, office_id
sales_products の sales_id、product_id
products の category_id
categories の taxonomy_id 

にそれぞれインデックスを設定する。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/08/16 11:40

    sales.dateにインデックスを付けたところ5sから2.5sに改善しましたが。
    そこからさらに上記インデックスを付けたところ110 msとびっくりするくらい早くなりました!本当にありがとうございます!

    キャンセル

+2

以下のような検討をされてみてはどうでしょうか。

  • SQLを投げる回数を減らしたい場合
    selectする項目が同じならば、UNIONクエリーでまとめれば1回で取得できます。

  • SQLの実行速度を速くしたい場合
    外部キーや抽出条件で使用しているorder.dateなどにインデックスを付けると速くなります。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/08/16 10:02

    ありがとうございます!
    4回executeしていたので、UNIONで1回にまとめてみたいと思います。
    インデックスも付けたことがなかったので、付けて試してみます。

    キャンセル

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

  • ただいまの回答率 88.33%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

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

  • トップ
  • PHPに関する質問
  • PHPでデータベースからデータを取得する際、とても時間がかかってしまうのを改善したい