いつもお世話になります。

商品数が20000点くらいです。
とにかく遅いのです。
スペックは
webサーバー
REDHAT
Celeron2.4
メモリ１G

SQLサーバー
REDHAT
MySQL 5
Celeron2.4
メモリ1G
2,3日中に、HPのDL140G3に変更予定。

MySQLのDBをすべてメモリ上におく方法があると聞いたことがありますが本当でしょうか？
でも、もしこのまま商品点数が増えて10万点なんてことになるとそれでも遅いような気がします。

何かよい方法はありませんでしょうか？

もしよろしければ、見てください。
http://pc-rabbit.jp/
よろしくお願いします。

PostgreSQL で試してみるとか. . .

# 遅いと嘆く前に, チューニングのしどころはたくさんありそうな気はします

あんまり詳しくないのですが。
多分これのことですね。

MEMORY (HEAP) ストレージエンジン

引用：
なんて書いてありますよ（汗）。
それにinnodbじゃなくて高速になったとしても、どうなんでしょう...

とりあえずmysql を高速化したいときに読むメモより

・key_buffer
・innodb_buffer_pool_size

のあたりをいじって様子をみてみるとか。

皆様、ありがとうございます。

＞mysqlを高速化したいときに読むメモより
こちらを実行して見ましたが、あまり、速度に変化はありませんでした。

DL140はfedoraのインストールでいま手こずっています。
Posgleも検討して見ますが扱ったことがないのでちょっと心配です。

困ってしまった状態です。
しかし、なぜ、管理画面の検索は早いのにフロントはおそいのでしょうか。

たとえば、これですと
http://pc-rabbit.jp/e-shop/products/detail.php?product_id=8524
IDで呼び出しているのですからただJOINしているだけだと思うのですがなぜ遅いのでしょう。

困りました。よろしくお願いします。

おはようございます. 大河内です.

引用：

ちょっと基本に戻りましょう...
「遅い」のには原因があるはずです.
まず, その原因を特定することから始めませんか？

少々大変ですが, ページを表示する際に, どんな処理が行われているか調査し, その処理にどれくらい時間がかかっているかを計測するのが良いと思います.

お世話になります。

＞少々大変ですが, ページを表示する際に, どんな処理
＞が行われているか調査し, その処理にどれくらい時間
＞がかかっているかを計測するのが良いと思います。
そうですね。ちょっと、大変なんでそれについては、もう
少し時間をいただきたいと思います。

根本的な解決にはならないですがWHEREで非表示の分は
計算しないようにしたらどうかと思うのですがどうでしょう？

PS.本日MySQLサーバーをHPのDL140G3に変更しました。
若干、早くなりました。

お世話になります。
logをとりましたのでちょっと大きいですが載せます。
#Time: 070912 12:49:29と# Time: 070912 12:51:08の分がカテゴリをクリックした場合

# Time: 070912 12:51:19の分が商品をクリックした場合

とりあえず、商品表示の部分は商品表示専用のSQL文を書けばいいと思うのですがいかがでしょう。
その場合detail.phpの変更はvw_products_allclass_detailの呼び出し部分の変更だけでしょうか。
どうしても必要なfieldを教えてください。
規格値がなければprice01_minなどはいらないような気がしますが
いかがでしょうか？

カテゴリについては見ても良くわかりません。

---------------------------------------------------------
/usr/local/mysql/libexec/mysqld, Version: 5.0.45-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 070912 12:49:29
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 126426
use ec_rabbit;
SELECT COUNT(*) FROM
(select straight_join
product_id,
product_code_min,
product_code_max,
price01_min,
price01_max,
price02_min,
price02_max,
stock_min,
stock_max,
stock_unlimited_min,
stock_unlimited_max,
del_flg,
status,
name,
comment1,
comment2,
comment3,
rank,
main_list_comment,
main_image,
main_list_image,
product_flag,
deliv_date_id,
sale_limit,
point_rate,
sale_unlimited,
create_date,
deliv_fee
,(select straight_join rank AS category_rank FROM dtb_category AS T4 WHERE T1.category_id = T4.category_id) as category_rank
,(select straight_join category_id AS sub_category_id FROM dtb_category T4 WHERE T1.category_id = T4.category_id) as category_id
FROM
dtb_products AS T1 RIGHT JOIN (select straight_join product_id AS product_id_sub, MIN(product_code) AS product_code_min, MAX(product_code) AS product_code_max, MIN(price01) AS price01_min, MAX(price01) AS price01_max, MIN(price02) AS price02_min, MAX(price02) AS price02_max, MIN(stock) AS stock_min, MAX(stock) AS stock_max, MIN(stock_unlimited) AS stock_unlimited_min, MAX(stock_unlimited) AS stock_unlimited_max FROM dtb_products_class GROUP BY product_id) AS T2 ON T1.product_id = T2.product_id_sub
) AS allcls WHERE del_flg = 0 AND status = 1 AND category_id IN ( 259,'260' ,'261' ,'262' ,'263' ,'264' ,'265' ,'266' ,'298' ,'312' ,'335' ,'371' ,'378' ,'388' ,'389' ,'390' ,'417' ,'431' ,'434' ,'267' ,'268' ,'269' ,'270' ,'271' ,'272' ,'273' ,'274' ,'275' ,'276' ,'277' ,'278' ,'279' ,'280' ,'281' ,'282' ,'283' ,'284' ,'285' ,'286' ,'287' ,'288' ,'289' ,'290' ,'291' ,'292' ,'293' ,'294' ,'295' ,'296' ,'297' ,'299' ,'300' ,'301' ,'302' ,'303' ,'304' ,'305' ,'306' ,'307' ,'308' ,'309' ,'310' ,'311' ,'313' ,'314' ,'315' ,'316' ,'317' ,'318' ,'319' ,'320' ,'321' ,'322' ,'323' ,'324' ,'325' ,'326' ,'327' ,'328' ,'329' ,'330' ,'331' ,'332' ,'333' ,'334' ,'336' ,'337' ,'338' ,'339' ,'340' ,'341' ,'342' ,'343' ,'344' ,'372' ,'373' ,'374' ,'375' ,'376' ,'377' ,'379' ,'380' ,'381' ,'382' ,'383' ,'384' ,'385' ,'386' ,'387' ,'418' ,'419' ,'420' ,'421' ,'422' ,'423' ,'424' ,'425' ,'426' ,'427' ,'428' ,'429' ,'430' ,'432' ,'433' ,'436' ,'437' ,'438' ,'439' ,'440' ,'441' ,'442' ,'443' ,'444' ,'445' ,'446' ,'447' ,'449' ,'450' ,'451' ,'452' ,'453' ,'454' ,'455' ,'456' ,'457' ,'458' ,'459' ,'460' ,'461' ,'462' ,'463' ,'464' ,'465' ,'466' ,'467' ,'468' ,'469' ,'470' ,'471' ,'472' ,'473' );
# Time: 070912 12:49:33
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 4 Lock_time: 0 Rows_sent: 15 Rows_examined: 126441
SELECT * FROM
(select straight_join
product_id,
product_code_min,
product_code_max,
price01_min,
price01_max,
price02_min,
price02_max,
stock_min,
stock_max,
stock_unlimited_min,
stock_unlimited_max,
del_flg,
status,
name,
comment1,
comment2,
comment3,
rank,
main_list_comment,
main_image,
main_list_image,
product_flag,
deliv_date_id,
sale_limit,
point_rate,
sale_unlimited,
create_date,
deliv_fee
,(select straight_join rank AS category_rank FROM dtb_category AS T4 WHERE T1.category_id = T4.category_id) as category_rank
,(select straight_join category_id AS sub_category_id FROM dtb_category T4 WHERE T1.category_id = T4.category_id) as category_id
FROM
dtb_products AS T1 RIGHT JOIN (select straight_join product_id AS product_id_sub, MIN(product_code) AS product_code_min, MAX(product_code) AS product_code_max, MIN(price01) AS price01_min, MAX(price01) AS price01_max, MIN(price02) AS price02_min, MAX(price02) AS price02_max, MIN(stock) AS stock_min, MAX(stock) AS stock_max, MIN(stock_unlimited) AS stock_unlimited_min, MAX(stock_unlimited) AS stock_unlimited_max FROM dtb_products_class GROUP BY product_id) AS T2 ON T1.product_id = T2.product_id_sub
) AS allcls WHERE del_flg = 0 AND status = 1 AND category_id IN ( 259,'260' ,'261' ,'262' ,'263' ,'264' ,'265' ,'266' ,'298' ,'312' ,'335' ,'371' ,'378' ,'388' ,'389' ,'390' ,'417' ,'431' ,'434' ,'267' ,'268' ,'269' ,'270' ,'271' ,'272' ,'273' ,'274' ,'275' ,'276' ,'277' ,'278' ,'279' ,'280' ,'281' ,'282' ,'283' ,'284' ,'285' ,'286' ,'287' ,'288' ,'289' ,'290' ,'291' ,'292' ,'293' ,'294' ,'295' ,'296' ,'297' ,'299' ,'300' ,'301' ,'302' ,'303' ,'304' ,'305' ,'306' ,'307' ,'308' ,'309' ,'310' ,'311' ,'313' ,'314' ,'315' ,'316' ,'317' ,'318' ,'319' ,'320' ,'321' ,'322' ,'323' ,'324' ,'325' ,'326' ,'327' ,'328' ,'329' ,'330' ,'331' ,'332' ,'333' ,'334' ,'336' ,'337' ,'338' ,'339' ,'340' ,'341' ,'342' ,'343' ,'344' ,'372' ,'373' ,'374' ,'375' ,'376' ,'377' ,'379' ,'380' ,'381' ,'382' ,'383' ,'384' ,'385' ,'386' ,'387' ,'418' ,'419' ,'420' ,'421' ,'422' ,'423' ,'424' ,'425' ,'426' ,'427' ,'428' ,'429' ,'430' ,'432' ,'433' ,'436' ,'437' ,'438' ,'439' ,'440' ,'441' ,'442' ,'443' ,'444' ,'445' ,'446' ,'447' ,'449' ,'450' ,'451' ,'452' ,'453' ,'454' ,'455' ,'456' ,'457' ,'458' ,'459' ,'460' ,'461' ,'462' ,'463' ,'464' ,'465' ,'466' ,'467' ,'468' ,'469' ,'470' ,'471' ,'472' ,'473' ) ORDER BY category_rank DESC, rank DESC LIMIT 15 OFFSET 0;
# Time: 070912 12:51:08
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 105356
SELECT product_class_id, classcategory_id1, classcategory_id2, class_id1, class_id2, stock, stock_unlimited FROM
(select straight_join * FROM
(select straight_join T3.product_class_id, T3.product_id AS product_id_sub, classcategory_id1, classcategory_id2,
T3.rank AS rank1, T4.rank AS rank2, T3.class_id AS class_id1, T4.class_id AS class_id2,
stock, price01, price02, stock_unlimited, product_code
FROM (select straight_join
T1.product_class_id,
T1.product_id,
classcategory_id1,
classcategory_id2,
T2.rank,
T2.class_id,
stock,
price01,
price02,
stock_unlimited,
product_code
FROM (dtb_products_class AS T1 LEFT JOIN dtb_classcategory AS T2
ON T1.classcategory_id1 = T2.classcategory_id))
AS T3 LEFT JOIN dtb_classcategory AS T4
ON T3.classcategory_id2 = T4.classcategory_id) AS T5 LEFT JOIN dtb_products AS T6
ON product_id_sub = T6.product_id) AS prdcls WHERE product_id = '13376' ORDER BY rank1 DESC, rank2 DESC;
# Time: 070912 12:51:14
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 9840619
SELECT * FROM
(select straight_join product_id,price01_min,price01_max,price02_min,price02_max,stock_min,stock_max,stock_unlimited_min,stock_unlimited_max,
del_flg,status,name,comment1,comment2,comment3,deliv_fee,main_comment,main_image,main_large_image,
sub_title1,sub_comment1,sub_image1,sub_large_image1,
sub_title2,sub_comment2,sub_image2,sub_large_image2,
sub_title3,sub_comment3,sub_image3,sub_large_image3,
sub_title4,sub_comment4,sub_image4,sub_large_image4,
sub_title5,sub_comment5,sub_image5,sub_large_image5,
product_flag,deliv_date_id,sale_limit,point_rate,sale_unlimited,file1,file2,category_id
FROM (select straight_join * FROM (dtb_products AS T1 RIGHT JOIN
(select straight_join
product_id AS product_id_sub,
MIN(price01) AS price01_min,
MAX(price01) AS price01_max,
MIN(price02) AS price02_min,
MAX(price02) AS price02_max,
MIN(stock) AS stock_min,
MAX(stock) AS stock_max,
MIN(stock_unlimited) AS stock_unlimited_min,
MAX(stock_unlimited) AS stock_unlimited_max
FROM dtb_products_class GROUP BY product_id) AS T2
ON T1.product_id = T2.product_id_sub ) ) AS T3 LEFT JOIN (select straight_join rank AS category_rank, category_id AS sub_category_id FROM dtb_category) AS T4
ON T3.category_id = T4.sub_category_id) AS alldtl WHERE product_id = '13376';
# Time: 070912 12:51:19
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 5 Lock_time: 0 Rows_sent: 0 Rows_examined: 126426
SELECT name, main_list_image, price01_min, price02_min, price01_max, price02_max, point_rate FROM
(select straight_join
product_id,
product_code_min,
product_code_max,
price01_min,
price01_max,
price02_min,
price02_max,
stock_min,
stock_max,
stock_unlimited_min,
stock_unlimited_max,
del_flg,
status,
name,
comment1,
comment2,
comment3,
rank,
main_list_comment,
main_image,
main_list_image,
product_flag,
deliv_date_id,
sale_limit,
point_rate,
sale_unlimited,
create_date,
deliv_fee
,(select straight_join rank AS category_rank FROM dtb_category AS T4 WHERE T1.category_id = T4.category_id) as category_rank
,(select straight_join category_id AS sub_category_id FROM dtb_category T4 WHERE T1.category_id = T4.category_id) as category_id
FROM
dtb_products AS T1 RIGHT JOIN (select straight_join product_id AS product_id_sub, MIN(product_code) AS product_code_min, MAX(product_code) AS product_code_max, MIN(price01) AS price01_min, MAX(price01) AS price01_max, MIN(price02) AS price02_min, MAX(price02) AS price02_max, MIN(stock) AS stock_min, MAX(stock) AS stock_max, MIN(stock_unlimited) AS stock_unlimited_min, MAX(stock_unlimited) AS stock_unlimited_max FROM dtb_products_class GROUP BY product_id) AS T2 ON T1.product_id = T2.product_id_sub
) AS allcls WHERE del_flg = 0 AND status = 1 AND (stock_max <> 0 OR stock_max IS NULL) AND product_id = '13376' ORDER BY RAND() LIMIT 3;

大河内です.

引用：

「どうしても必要な field」 は, 要件によって異なると思われますので, 教えてくださいと言われても困惑してしまいます...

引用：

category_id が連番なのにも関わらず, すべて IN 句に入っているのが気になります.
この個所を除いた SQL の実行時間を計測してみて, 改善されるようなら, そこがボトルネックなのでしょう.

はずしていたら申し訳ないです...

お世話になります。

少し、進展しました。
商品詳細表示のところですが

vw_product_classは何もしていないので
4秒→4秒変わらず
vw_products_allclassが
5秒→3秒
vw_products_allclass_detailが
6秒→4秒

若干、早くなりました。

次は、大幅に速度アップを狙って見ます。


# Time: 070915 10:23:12
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 105356
SELECT product_class_id, classcategory_id1, classcategory_id2, class_id1, class_id2, stock, stock_unlimited FROM
(select straight_join * FROM
(select straight_join T3.product_class_id, T3.product_id AS product_id_sub, classcategory_id1, classcategory_id2,
T3.rank AS rank1, T4.rank AS rank2, T3.class_id AS class_id1, T4.class_id AS class_id2,
stock, price01, price02, stock_unlimited, product_code
FROM (select straight_join
T1.product_class_id,
T1.product_id,
classcategory_id1,
classcategory_id2,
T2.rank,
T2.class_id,
stock,
price01,
price02,
stock_unlimited,
product_code
FROM (dtb_products_class AS T1 LEFT JOIN dtb_classcategory AS T2
ON T1.classcategory_id1 = T2.classcategory_id))
AS T3 LEFT JOIN dtb_classcategory AS T4
ON T3.classcategory_id2 = T4.classcategory_id) AS T5 LEFT JOIN dtb_products AS T6
ON product_id_sub = T6.product_id) AS prdcls WHERE product_id = '17527' ORDER BY rank1 DESC, rank2 DESC;
# Time: 070915 10:23:16
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 9840619
SELECT * FROM
(SELECT STRAIGHT_JOIN product_id,
del_flg,status,name,comment1,comment2,comment3,deliv_fee,main_comment,main_image,main_large_image,
sub_title1,sub_comment1,sub_image1,sub_large_image1,
sub_title2,sub_comment2,sub_image2,sub_large_image2,
sub_title3,sub_comment3,sub_image3,sub_large_image3,
sub_title4,sub_comment4,sub_image4,sub_large_image4,
sub_title5,sub_comment5,sub_image5,sub_large_image5,
product_flag,deliv_date_id,sale_limit,point_rate,sale_unlimited,file1,file2,category_id
FROM ( SELECT STRAIGHT_JOIN * FROM (dtb_products AS T1 RIGHT JOIN
(SELECT STRAIGHT_JOIN
product_id AS product_id_sub



FROM dtb_products_class ) AS T2
ON T1.product_id = T2.product_id_sub ) ) AS T3 LEFT JOIN (SELECT STRAIGHT_JOIN rank AS category_rank, category_id AS sub_category_id FROM dtb_category) AS T4
ON T3.category_id = T4.sub_category_id) AS alldtl WHERE product_id = '17527';
# Time: 070915 10:23:19
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 3 Lock_time: 0 Rows_sent: 1 Rows_examined: 126428
SELECT name, main_list_image, price01_min, price02_min, price01_max, price02_max, point_rate FROM
(SELECT STRAIGHT_JOIN
product_id,
product_code_min,
product_code_max,
price01_min,
price01_max,
price02_min,
price02_max,
stock_min,
stock_max,
stock_unlimited_min,
stock_unlimited_max,

del_flg,
status,
name,

comment1,
comment2,
comment3,
rank,
main_list_comment,
main_image,
main_list_image,
product_flag,
deliv_date_id,
sale_limit,
point_rate,
sale_unlimited,
create_date,
deliv_fee
,(SELECT STRAIGHT_JOIN rank AS category_rank FROM dtb_category AS T4 WHERE T1.category_id = T4.category_id) as category_rank
,(SELECT STRAIGHT_JOIN category_id AS sub_category_id FROM dtb_category T4 WHERE T1.category_id = T4.category_id) as category_id
FROM
dtb_products AS T1 RIGHT JOIN
(SELECT STRAIGHT_JOIN
product_id AS product_id_sub ,
product_code as product_code_min,
product_code as product_code_max,
price01 as price01_min,
price01 as price01_max,
price02 as price02_min,
price02 as price02_max,
stock as stock_min,
stock as stock_max,
stock_unlimited as stock_unlimited_min,
stock_unlimited as stock_unlimited_max
FROM dtb_products_class)
AS T2 ON T1.product_id = T2.product_id_sub
) AS allcls WHERE del_flg = 0 AND status = 1 AND (stock_max <> 0 OR stock_max IS NULL) AND product_id = '17527' ORDER BY RAND() LIMIT 3;

だいぶ早くなりました。

商品詳細のところですが表示までの時間がだいぶ早くなりました。
vw_product_classは規格値が必要ないので最小限にしましたので1秒以下です
4秒→0秒
vw_products_allclassが
5秒→3秒
vw_products_allclass_detailが
6秒→4秒

DL140G3での速度は詳細の表示に約2秒ですので遅いですが待ってもらえるレベルかと思います。
心配なのは内容が良くわからない私が大幅にSQLを書き換えてしまったので正確に動いているのかが非常に心配です。
ご指摘がありましたら、ご教授いただけると助かります。

規格値のところは次のように変更しました。
/* 商品規格情報の取得 */
function lfGetProductsClass($product_id) {
$arrRet = array();
if(sfIsInt($product_id)) {
// 商品規格取得

$objQuery = new SC_Query();
$arrRet = $objQuery->select("*", "vw_product_class2 AS alldtl", "product_id = ?", array($product_id));
}
return $arrRet;
}
---------------------------------------
"vw_product_class2" => '
(select * FROM dtb_products_class)',
---------------------------------------

よろしくお願いします。m(__)m



# Time: 070915 17:46:58
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 84401
use ec_rabbit;
SELECT * FROM
(SELECT STRAIGHT_JOIN
dtb_products.product_id,
dtb_products.del_flg,
dtb_products.status,
dtb_products.name,
dtb_products.comment1,
dtb_products.comment2,
dtb_products.comment3,
dtb_products.deliv_fee,
dtb_products.main_comment,
dtb_products.main_image,
dtb_products.main_large_image,
dtb_products.sub_title1,
dtb_products.sub_comment1,
dtb_products.sub_image1,
dtb_products.sub_large_image1,
dtb_products.sub_title2,
dtb_products.sub_comment2,
dtb_products.sub_image2,
dtb_products.sub_large_image2,
dtb_products.sub_title3,
dtb_products.sub_comment3,
dtb_products.sub_image3,
dtb_products.sub_large_image3,
dtb_products.sub_title4,
dtb_products.sub_comment4,
dtb_products.sub_image4,
dtb_products.sub_large_image4,
dtb_products.sub_title5,
dtb_products.sub_comment5,
dtb_products.sub_image5,
dtb_products.sub_large_image5,
dtb_products.product_flag,
dtb_products.deliv_date_id,
dtb_products.sale_limit,
dtb_products.point_rate,
dtb_products.sale_unlimited,
dtb_products.file1,
dtb_products.file2,
dtb_products.category_id,

dtb_products_class.price02 AS price02_max,
dtb_products_class.price02 AS price02_min,
dtb_products_class.stock AS stock_max,
dtb_products_class.stock AS stock_min,
dtb_products_class.stock_unlimited AS stock_unlimited_max,
dtb_products_class.stock_unlimited AS stock_unlimited_min,

dtb_category.rank AS category_rank,
dtb_category.category_id AS sub_category_id

FROM dtb_products
LEFT JOIN dtb_products_class ON dtb_products.product_id = dtb_products_class.product_id
LEFT JOIN dtb_category ON dtb_products.category_id = dtb_category.category_id
) AS alldtl WHERE product_id = '17518';
# Time: 070915 17:47:01
# User@Host: honma[honma] @ ns200.hoge.jp [192.168.11.200]
# Query_time: 3 Lock_time: 0 Rows_sent: 1 Rows_examined: 84403
SELECT name, main_list_image, price01_min, price02_min, price01_max, price02_max, point_rate FROM
(SELECT STRAIGHT_JOIN
dtb_products.product_id,
dtb_products.del_flg,
dtb_products.status,
dtb_products.name,
dtb_products.comment1,
dtb_products.comment2,
dtb_products.comment3,
dtb_products.rank,
dtb_products.main_list_comment,
dtb_products.main_image,
dtb_products.main_list_image,
dtb_products.product_flag,
dtb_products.deliv_date_id,
dtb_products.sale_limit,
dtb_products.point_rate,
dtb_products.sale_unlimited,
dtb_products.create_date,
dtb_products.deliv_fee,
dtb_products_class.product_code AS product_code_min,
dtb_products_class.product_code AS product_code_max,
dtb_products_class.price01 AS price01_min,
dtb_products_class.price01 AS price01_max,
dtb_products_class.price02 AS price02_min,
dtb_products_class.price02 AS price02_max,
dtb_products_class.stock AS stock_min,
dtb_products_class.stock AS stock_max,
dtb_products_class.stock_unlimited AS stock_unlimited_min,
dtb_products_class.stock_unlimited AS stock_unlimited_max,
dtb_products_class.product_id AS product_id_sub,
dtb_category.rank AS category_rank,
dtb_category.category_id

FROM dtb_products
LEFT JOIN dtb_products_class ON dtb_products.product_id = dtb_products_class.product_id
LEFT JOIN dtb_category ON dtb_products.category_id = dtb_category.category_id
) AS allcls WHERE del_flg = 0 AND status = 1 AND (stock_max <> 0 OR stock_max IS NULL) AND product_id = '17518' ORDER BY RAND() LIMIT 3;