[MySQL]ECサイトのデータ取り扱いについて

Laravelを使用した150万件程の商品を取り扱う想定のシステムを組んでおります。
当初、1万件程の商品の取扱いであったため問題ありませんでしたが、商品数が増えたためデータ取得に時間がかかってしまいます。
少しでも処理速度を早くするためにアドバイスをお願いしたいです。

商品テーブル（products）は以下の通りです。
id（bigint,auto_increment）
name（varchar(255)）//商品名
open_type（enum('O','P')）//公開中or非公開（公開中：O、非公開：P）
post_status（enum('D','P')）//公開中or下書き（公開中：D、非公開：P）
file_path（varchar(255)）//画像のパス
price（varchar(255)）//金額
explaanation（text）//商品説明
is_recommend（tinyint(1)）//おすすめ商品フラグ（デフォルト：0、おすすめの場合：1）
created_at
updated_at
deleted_at

サイトTOPページ表示時と、サイト内で商品をフリーワードで検索した際の表示の改善が必要です。

サイトTOPページ表示時、おすすめの商品を取得しているのですが、
現状は150万件程のレコードがあるテーブルから、is_recommendカラムが”1”かつpost_statusカラムとopen_typeカラムが公開中のレコードを取得しています。
改善するために、別途おすすめ商品テーブルを作成したほうが良いのではと考えております。

商品の検索に関して、ユーザーが入力した文字から、商品テーブルのnameカラムに対して
LIKE　％”文字列”％で検索をして、取得した商品を画面に表示しています。データ取得のコードは以下のような形です。

php
1$query = Product::where('open_type', 'O')
2              ->where('post_status', 'D')
3              ->where('name', 'LIKE', "%'検索文字列'%");
4$ProductList = $query->orderBy('is_recommend', 'DESC')->paginate(48);

クエリなどのアドバイスを頂けますと大変助かります。
よろしくお願いいたします。

行動規範の内容に同意します

回答1件

ベストアンサー

対策
・DB ではなく全文検索エンジンを使う。これが大本命。例えば AWS の ElasticSearch や CloudSearch など。
・MySQL の fulltext index を使う。
・おすすめ商品など、事前作成できるものは作成しておく。
・一度生成したデータは一定時間キャッシュして使い回す。

クエリの高速化については、まずは explain してインデックス使用状況などを分析してください。

投稿2022/05/13 14:28