MySQL Forums
Forum List  »  Optimizer & Parser

query cache & Using temporary
Posted by: Tsvetomir Denev
Date: May 02, 2008 09:51AM

I got this query:

mysql> EXPLAIN Select
-> V.col_title, V.col_artist_id, V.col_genre_id, V.col_lang_id, V.col_lang2_id, V.col_year
-> , A.col_name artist_name
-> , FN.col_duration duration
-> , M.col_id member_id, M.col_login member_login
->
-> From tb_videos V
-> Left Join tb_artists A On V.col_artist_id = A.col_id
-> Left Join tb_filenames FN On V.col_id = FN.col_video_id
-> Left Join tb_member_videos MV On V.col_id = MV.col_video_id
-> Left Join tb_members M On MV.col_member_id = M.col_id
->
-> Where V.col_status = '1'
-> Group By V.col_id
->
-> Order By V.col_date_added DESC, V.col_id DESC
-> Limit 0,15;
+----+-------------+-------+--------+---------------+------------+---------+---------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------------+---------+---------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | V | range | col_status | col_status | 2 | NULL | 2227 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | A | eq_ref | PRIMARY | PRIMARY | 4 | db_musicplayon.V.col_artist_id | 1 | |
| 1 | SIMPLE | FN | eq_ref | PRIMARY | PRIMARY | 4 | db_musicplayon.V.col_id | 1 | |
| 1 | SIMPLE | MV | index | NULL | PRIMARY | 8 | NULL | 2229 | Using index |
| 1 | SIMPLE | M | eq_ref | PRIMARY | PRIMARY | 4 | db_musicplayon.MV.col_member_id | 1 | |
+----+-------------+-------+--------+---------------+------------+---------+---------------------------------+------+----------------------------------------------+
5 rows in set (0.00 sec)


Is this query cached and why "Using temporary; Using filesort", because after a while:


| 581 | root | localhost | db | Query | 33 | Copying to tmp table | Select SQL_CALC_FOUND_ROWS
V.*
, A.col_name artist_name
, FN.col_duration duration
, M.col_id member_id, M.col_login member_login

From tb_videos V
Left Join tb_artists A On V.col_artist_id = A.col_id
Left Join tb_filenames FN On V.col_id = FN.col_video_id
Left Join tb_member_videos MV On V.col_id = MV.col_video_id
Left Join tb_members M On MV.col_member_id = M.col_id

Where V.col_status = '1'
Group By V.col_id

Order By V.col_date_added DESC, V.col_id DESC
Limit 0,15 |



I appreciate any help!

Options: ReplyQuote


Subject
Views
Written By
Posted
query cache & Using temporary
4059
May 02, 2008 09:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.