• 締切済み

MYSQLチューニング初心者です。

MYSQLチューニング初心者です。 MYSQLでEXPLAINでチューニングをしている際以下の壁にぶち当たって打開方法がないか模索しております。 テーブル構造はダミーですがたとえばa1,a2,a3というテーブル下記記載 SELECT * from a1 left join a2 on a1.id = a2.id left join a3 on a3.id = a1.mailbox_id \G a1.id: INT 主キー a1.mailbox_id: INT INDEX a1.title: varchar(50) a1.body: text a1.created_at: datetime a2.id: INT 主キー a2.mail_address: varchar(50) INDEX a3.id: INT 主キー a3.priority: INT a3.mail_box_name: varchar(50) 上記テーブル構造にて（EXPLAIN SELECT a1.id as id,a1.title,a1.body,a1.created_at,a2.mail_address,a3.mail_box_name FROM a1 left join a2 on a1.id = a2.id left join a3 on a3.id = a1.mailbox_id WHERE a2.mail_address = 'hogehoge@test.jp' order by created_at DESC;）を行うと、 Using temporary; Using filesort；が必ず出てきて対処方法に詰まっています。 order句のcreated_atをINDEXにすればよいのかな？と思いつけてみたのですが、a1.idのプライマリキーが優先され、結局同じ症状が出ます。 マルチインデックスでa1.id,created_atを付加し、FORCE INDEXをしたときはUsing temporary; Using filesortは消えますが、ExplainのrefがNULLになってしまいます。 方向性としてはtemporaryを使わない、indexでのsortを可能にする方法です。 まとまっていない文章で申し訳ございません。どうぞご教授のほどよろしくお願いします。 ＞＞データ量としてはa1・a2は50万レコード　a3は10件ほどです。 ＞＞mysql Ver 14.14 Distrib 5.1.40, for redhat-linux-gnu (i686) using readline 5.1

  • MySQL
  • 回答数1
  • ありがとう数2

みんなの回答

  • yambejp
  • ベストアンサー率51% (3827/7415)
回答No.1

考え方だけ。 複数テーブルを結合した結果をソートしているんだから テンポラリが発生するのは当然。 そもそもorder by する必要があるのかどうか疑問？ ページングしたいならorderbyではない方法でランク付けして、 範囲を指定して処理すればよいでしょうし。 もしくは参照頻度が高くデータ更新が必ずしも高くない処理なら 結果を通常テーブルをつくっておいてそれにバルクで流し込みをして、 ソート用フィールドにindexを貼っておくとよいです。 ユーザーはデータ更新を任意のタイミングですることで、 完全なリアルタイムではないですが、非常に高速な仕組みになります。

sshota82
質問者

補足

yambejpさん、ご回答ありがとうございます。 ＞＞そもそもorder by する必要があるのかどうか疑問？ order_byをかけている理由ですが、最新のデータからどうしても5件取り出し、アプリケーション側に表示させたいため行っております。idと日付の順序も現在DB上ではそろっていないため、こちらも悩ましく思ってます。 ＞＞もしくは参照頻度が高くデータ更新が必ずしも高くない処理なら結果を通常テーブルをつくっておいてそれにバルクで流し込みをして、ソート用フィールドにindexを貼っておくとよいです。 更新用テーブルから参照用テーブル（結合されたDBデータを格納）してwhere ～ odrderby～で解決するということですね。それなら単一テーブルで高速化が実現できそうです。 ただ今回既にに使用されているアプリケーションとなっているため、DBのチューニングでどうにか出来れば！！と思っておりました。 JOINをした場合にはorder byではINDEXを付けたとしてもUsing temporaryは発生するんですね。 今のDBそのままの構造で解決できるのはwhere句指定のカラムにINDEXを付けることだけが最良なのでしょうか。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. MySQL

関連するQ&A

  • mysqlでJOIN文

    関係有りそうな部分だけ抜粋します。 mysqlで外部キーを設定して、 下記のようなSQLを実行してみましたが、 #1066 - Not unique table/alias: 'tie_up'と エラーメッセージが表示されました。 何がおかしいのでしょうか？ 外部キーは「song」テーブルの「tie_up_1_ID」、同「song」テーブルの「tie_up_2_ID」、 それと「tie_up」テーブルの「tie_up」です。 SQLを触るの5年振りなんで・・・。 SELECT * FROM ((((( song left JOIN kashu ON song.kashu_ID = kashu.kashu_ID) left JOIN sakushi ON song.sakushi_ID = sakushi.sakushi_ID) left JOIN sakkyoku ON song.sakkyoku_ID = sakkyoku.sakkyoku_ID) left JOIN hennkyoku ON song.hennkyoku_ID = hennkyoku.hennkyoku_ID) left JOIN tie_up ON song.tie_up_1_ID = tie_up.tie_up_ID) left JOIN tie_up ON song.tie_up_2_ID = tie_up.tie_up_ID;

    • ベストアンサー
    • MySQL
  • mysqlで表の結合下記のように書きましたが

    mysqlで表の結合。 下記のように書きましたが、うまく動きません。 「left JOIN kashu ka2 ON song.kashu_2_ID = ka2.kashu_ID」 「ka2.kashu as kashu2」 ↑この部分、2つを追加しました。 何も表示されませんでした。 上の二つを削除すると動きます。 何がいけないのでしょうか？ mysql_query("SELECT song.song_ID, song_name, song_name_yomi, kashu, kashu_yomi, sakushi, sakushi_yomi, sakkyoku, sakkyoku_yomi, hennkyoku, hennkyoku_yomi, tie_up.tie_up, ti2.tie_up as tie_up2, ka2.kashu as kashu2 FROM (((((( song left JOIN kashu ON song.kashu_1_ID = kashu.kashu_ID) left JOIN kashu ka2 ON song.kashu_2_ID = ka2.kashu_ID) left JOIN sakushi ON song.sakushi_ID = sakushi.sakushi_ID) left JOIN sakkyoku ON song.sakkyoku_ID = sakkyoku.sakkyoku_ID) left JOIN hennkyoku ON song.hennkyoku_ID = hennkyoku.hennkyoku_ID) left JOIN tie_up ON song.tie_up_1_ID = tie_up.tie_up_ID) left JOIN tie_up ti2 ON song.tie_up_2_ID = ti2.tie_up_ID where song_name like '%%'");

    • ベストアンサー
    • MySQL
  • 助けて＞＜ 3つのテーブルで簡単な集計

    下記のような３種類のテーブルがあります。 これを集計して、指定したユーザーの指定月のアクセス日時と売上げ金額を表にしたいと思っています。 こんな感じに。 +--+--------+----------+-------+-----+ | id | username| date 　 | access | sales | +--+--------+----------+-------+-----+ | 1 | admin | 2010-11-13 | 　 2 |　5000 | | 1 | admin | 2010-11-14 | 　 2 |　5000 | | 1 | admin | 2010-11-15 | 　1 | 20000 | +--+--------+----------+-------+-----+ SELECT u.id AS id, u.username, DATE(a.created_at) AS date, COUNT(*) AS access, SUM(s.amount) AS sales FROM sf_guard_user u INNER JOIN sales s ON u.username = s.user_id LEFT JOIN access_log a ON u.username = a.user_id WHERE (u.username = 'admin' AND a.created_at > '2010-11-01 00:00:00' AND a.created_at < '2010-11-30 23:59:59') GROUP BY date ORDER BY a.created_at; とやってみたのですがダメでした；； ■ユーザー情報テーブル mysql> select id,username from user; +--+---------+ | id | username | +--+---------+ |　1 | admin 　　| +--+---------+ ■アクセスログテーブル mysql> select * from access_log; +--+-------+--------+------------------+ | id | user_id | ip　　　　 | created_at　 　　　　| +--+-------+--------+------------------+ |　1 | admin　 | 127.0.0.1 | 2010-11-13 21:56:54 | |　2 | admin　 | 127.0.0.1 | 2010-11-13 21:56:54 | |　3 | admin　 | 127.0.0.1 | 2010-11-14 21:56:54 | |　4 | admin　 | 127.0.0.1 | 2010-11-14 21:56:54 | |　5 | admin　 | 127.0.0.1 | 2010-11-15 21:56:54 | +--+-------+--------+------------------+ ■売上げ金額テーブル mysql> select * from sales; +--+-------+------+------------------+ | id | user_id | amount| created_at　 　　　　| +--+-------+------+------------------+ |　1 | admin　 |　 5000 | 2010-11-13 21:56:54 | |　2 | admin　 |　 5000 | 2010-11-14 21:56:54 | |　3 | admin　 |　 5000 | 2010-11-15 21:56:54 | |　4 | admin　 |　 5000 | 2010-11-15 21:56:54 | +--+-------+------+------------------+

    • ベストアンサー
    • MySQL
  • MySQLでLEFT JOIN

    MySQL4.0.26でテーブルの左外部結合がしたいです。 左テーブルAと 右テーブルBをWHERE句で抽出したもの を結合することはできないのでしょうか？ 考えた以下の式ではエラーが出てしまいます。 SELECT * FROM A LEFT JOIN ( SELECT * B WHERE id = 1 ) AS B2 ON A.id = B2.id

    • ベストアンサー
    • MySQL
  • mysqlでのsql文

    下記の'←追加'と書いた部分を追加して実行しましたが、動いてくれません。 ご教授お願いします。 ("SELECT song.song_ID, song.song_name, song.song_name_yomi, kashu.kashu, ka2.kashu as kashu2, ka3.kashu as kashu3, ka4.kashu as kashu4, ka5.kashu as kashu5, ka6.kashu as kashu6, ka7.kashu as kashu7, ka8.kashu as kashu8, ka9.kashu as kashu9, ka10.kashu as kashu10, kashu.kashu_yomi, sakushi, sakushi_yomi, sakkyoku, sakkyoku_yomi, hennkyoku, hennkyoku_yomi, tie_up.tie_up, ti2.tie_up as tie_up2 kashu_yomi, ←追加 ky2.kashu_yomi as kashu_yomi2 ←追加 FROM (((((((((((((((( song left JOIN kashu ON song.kashu_1_ID = kashu.kashu_ID) left JOIN kashu_yomi ON song.kashu_1_ID = kashu.kashu_ID) ←追加 left JOIN kashu_yomi ky2 ON song.kashu_2_ID = ky2.kashu_ID) ←追加 left JOIN kashu ka2 ON song.kashu_2_ID = ka2.kashu_ID) left JOIN kashu ka3 ON song.kashu_3_ID = ka3.kashu_ID) left JOIN kashu ka4 ON song.kashu_4_ID = ka4.kashu_ID) left JOIN kashu ka5 ON song.kashu_5_ID = ka5.kashu_ID) left JOIN kashu ka6 ON song.kashu_6_ID = ka6.kashu_ID) left JOIN kashu ka7 ON song.kashu_7_ID = ka7.kashu_ID) left JOIN kashu ka8 ON song.kashu_8_ID = ka8.kashu_ID) left JOIN kashu ka9 ON song.kashu_9_ID = ka9.kashu_ID) left JOIN kashu ka10 ON song.kashu_10_ID = ka10.kashu_ID) left JOIN sakushi ON song.sakushi_ID = sakushi.sakushi_ID) left JOIN sakkyoku ON song.sakkyoku_ID = sakkyoku.sakkyoku_ID) left JOIN hennkyoku ON song.hennkyoku_ID = hennkyoku.hennkyoku_ID) left JOIN tie_up ON song.tie_up_1_ID = tie_up.tie_up_ID) LEFT JOIN tie_up ti2 ON song.tie_up_2_ID = ti2.tie_up_ID where song_name like '%%'" ))

    • ベストアンサー
    • MySQL
  • MySQLでエラーがでます

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"8cb2237d0679ca88db6464eac60da96345513964", created"2011-10-05 15:16:04"' at line 1 PHPをブラウザで開いて動作確認すると上記のようにエラーがでてしまいます。 dbはこんな感じで作りました。 CREATE TABLE `class_orms`.`members` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 255 ) NOT NULL , `email` VARCHAR( 255 ) NOT NULL , `password` VARCHAR( 100 ) NOT NULL , `created` DATETIME NOT NULL , `modified` TIMESTAMP NOT NULL ) ENGINE = MYISAM ; CREATE TABLE `class_orms`.`posts` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `message` TEXT NOT NULL , `member_id` INT NOT NULL , `reply_post_id` INT NOT NULL , `created` DATETIME NOT NULL , `modified` TIMESTAMP NOT NULL ) ENGINE = MYISAM ; SQL側の1行目でエラーがあるということなのでしょうか？ どなたか回答よろしくお願いします。 動作環境------------------- Apache/2.2.14 (Win32) DAV/2 mod_ssl/2.2.14 OpenSSL/0.9.8l mod_autoindex_color PHP/5.3.1 phpMyAdmin バージョン情報: 3.2.4

    • ベストアンサー
    • MySQL
  • php mysqlのクエリでエラー

    「←追加」と追加したのですが、Warning: Invalid argument supplied for foreach()とエラーがでました。 調べたり、試行錯誤してみましたがダメでした。 何が間違っているのでしょうか？ try{ $dbh = new PDO($dsn, $user, $password); print('接続に成功しました。<br>'); $dbh->query('SET NAMES utf8'); $sql = ('SELECT song.number, song.song_title, artist.artist, ka2.artist as artist_2, ka3.artist as artist_3, ka4.artist as artist_4, ka5.artist as artist_5, ka6.artist as artist_6, ka7.artist as artist_7, ka8.artist as artist_8, ka9.artist as artist_9, ka10.artist as artist_10, ka11.artist as artist_11, ka12.artist as artist_12, sakushi.sakushi, ss2.sakushi as sakushi_2, ss3.sakushi as sakushi_3, ss4.sakushi as sakushi_4, sakkyoku.sakkyoku, sk2.sakkyoku as sakkyoku_2, sk3.sakkyoku as sakkyoku_3, sk4.sakkyoku as sakkyoku_4, hennkyoku.hennkyoku, hk2.hennkyoku as hennkyoku_2, hk3.hennkyoku as hennkyoku_3, hk4.hennkyoku as hennkyoku_4, genre, gr2.genre as genre_2,←追加 tie_up, part FROM (((((((((((((((((((((((((((( song LEFT JOIN artist ON song.artist_1 = artist.artist_ID) LEFT JOIN artist ka2 ON song.artist_2 = ka2.artist_ID) LEFT JOIN artist ka3 ON song.artist_3 = ka3.artist_ID) LEFT JOIN artist ka4 ON song.artist_4 = ka4.artist_ID) LEFT JOIN artist ka5 ON song.artist_5 = ka5.artist_ID) LEFT JOIN artist ka6 ON song.artist_6 = ka6.artist_ID) LEFT JOIN artist ka7 ON song.artist_7 = ka7.artist_ID) LEFT JOIN artist ka8 ON song.artist_8 = ka8.artist_ID) LEFT JOIN artist ka9 ON song.artist_9 = ka9.artist_ID) LEFT JOIN artist ka10 ON song.artist_10 = ka10.artist_ID) LEFT JOIN artist ka11 ON song.artist_11 = ka11.artist_ID) LEFT JOIN artist ka12 ON song.artist_12 = ka12.artist_ID) LEFT JOIN sakushi ON song.sakushi_1 = sakushi.sakushi_ID) LEFT JOIN sakushi ss2 ON song.sakushi_2 = ss2.sakushi_ID) LEFT JOIN sakushi ss3 ON song.sakushi_3 = ss3.sakushi_ID) LEFT JOIN sakushi ss4 ON song.sakushi_4 = ss4.sakushi_ID) LEFT JOIN sakkyoku ON song.sakkyoku_1 = sakkyoku.sakkyoku_ID) LEFT JOIN sakkyoku sk2 ON song.sakkyoku_2 = sk2.sakkyoku_ID) LEFT JOIN sakkyoku sk3 ON song.sakkyoku_3 = sk3.sakkyoku_ID) LEFT JOIN sakkyoku sk4 ON song.sakkyoku_4 = sk4.sakkyoku_ID) LEFT JOIN hennkyoku ON song.hennkyoku_1 = hennkyoku.hennkyoku_ID) LEFT JOIN hennkyoku hk2 ON song.hennkyoku_2 = hk2.hennkyoku_ID) LEFT JOIN hennkyoku hk3 ON song.hennkyoku_3 = hk3.hennkyoku_ID) LEFT JOIN hennkyoku hk4 ON song.hennkyoku_4 = hk4.hennkyoku_ID) LEFT JOIN genre ON song.genre_1 = genre.genre_ID) LEFT JOIN genre gr2 ON song.genre_2 = gr2.genre_ID)←追加 LEFT JOIN tie_up ON song.tie_up_1 = tie_up.tie_up_ID) LEFT JOIN part ON song.part_1 = part.part_ID) ;');

  • MYSQLでSQLSERVERのリンクサーバーのようなことは可能ですか

    MYSQLでSQLSERVERのリンクサーバーのようなことは可能ですか？ AとBのMYSQLサーバーがあり、AのDB接続からBのDBを参照したいのですが、方法はありますでしょうか？ 一つのSQL文にて下記のようなことを実現したいです。 SELECT * FROM servernamea.dbname.tablename LEFT JOIN servernameb.dbname.tablename ON servernamea.dbname.tablename.id = servernameb.dbname.tablename.id どなたかよろしくお願いします。

    • ベストアンサー
    • MySQL
  • SQLで、アクセス集計について困っています。

    アクセス集計について困っています。 mysql> desc access_log; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | user_id | varchar(50) | YES | | NULL | | | ip | varchar(15) | YES | | NULL | | | created_at | datetime | NO | | NULL | | | updated_at | datetime | NO | | NULL | | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.02 sec) mysql> select * from access_log; +----+---------+-----------+---------------------+---------------------+ | id | user_id | ip | created_at | updated_at | +----+---------+-----------+---------------------+---------------------+ | 1 | admin | 127.0.0.1 | 2010-11-13 21:56:54 | 2010-11-13 23:07:27 | | 2 | admin | 127.0.0.1 | 2010-11-13 21:56:54 | 2010-11-13 23:07:27 | | 3 | admin | 127.0.0.1 | 2010-11-14 21:56:54 | 2010-11-13 23:07:27 | | 4 | admin | 127.0.0.1 | 2010-11-14 21:56:54 | 2010-11-13 23:07:27 | | 5 | admin | 127.0.0.1 | 2010-11-15 21:56:54 | 2010-11-13 23:07:27 | +----+---------+-----------+---------------------+---------------------+ 5 rows in set (0.00 sec) 上記のように定義したテーブルがあります。 SELECT a.id, a.user_id, COUNT(*), DATE(a.created_at) AS date FROM access_log a WHERE (a.user_id = 'admin' AND a.created_at > '2010-11-01 00:00:00' AND a.created_at < '2010-11-29 23:59:59') GROUP BY date ORDER BY a.created_at; のようにして日別のアクセス数の集計をしています。 ここから、同じ日の同一IPのアクセスは1アクセスとして計算したいのですが、 どのようにSQLを書けばよいでしょうか？ よろしくお願いいたします。

    • ベストアンサー
    • MySQL
  • MYSQLの差集合について

    MYSQLの差集合について MYSQLの差集合についてお教えください。OracleにはMinus、Postgres等はexceptがあります。MYSQLで同等の関数がないか調べましたが、無いのか検索がヘタなのかヒットしません。下記のようにSQLを書けばexceptと同じ効果があるのはわかりますが、exceptみたいにかけたら簡単かなと思います。そのような関数がありましたら、お教えください。よろしくお願い致します。 mysql> SELECT table1.* FROM table1 -> LEFT JOIN table2 ON table1.id=table2.id -> WHERE table2.id IS NULL;

    • ベストアンサー
    • MySQL

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する