Re: Performance of Views

[Scott Sosna]

Here it is: the first SQL statement is what was used for a CREATE VIEW, not specifying an algorithm or anything like that. The tables are InnoDB, the only large table is SONG with over 19,000 rows, CD has about 1500, artist has about 2600.

Thanks.
-scs
----------------------------------------------------

mysql> explain
-> SELECT
-> cd.cd_seq_id,
-> cd.name cd_name,
-> cd.last_played_on_date cd_last_played_on_date,
-> a.artist_seq_id,
-> a.last_name,
-> a.first_name,
-> a.display_name artist_display_name,
-> a.artist_url,
-> a.last_played_on_date artist_last_played_on_date,
-> s.song_seq_id,
-> s.song_nbr,
-> REPLACE(s.name,'<','&lt;') song_name,
-> s.song_time,
-> s.allmusic_url song_allmusic_url,
-> s.last_played_on_date song_last_played_on_date,
-> l.label_code,
-> l.label_name,
-> REPLACE(cd.label_id,'<','&lt;') label_id,
-> l.label_url
-> FROM
-> cd,
-> song s,
-> artist a,
-> cd_label l
-> WHERE
-> cd.cd_seq_id = s.cd_seq_id AND
-> IF (s.artist_seq_id IS NOT NULL, s.artist_seq_id, cd.artist_seq_id) = a.artist_seq_id AND
-> cd.label_code = l.label_code
-> ORDER BY
-> cd.cd_seq_id,
-> s.song_nbr
-> \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cd
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1795
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: l
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 5
ref: radiodev.cd.label_code
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ref
possible_keys: song_u_01,song_i_03
key: song_i_03
key_len: 4
ref: radiodev.cd.cd_seq_id
rows: 5
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
4 rows in set (0.01 sec)

mysql> explain select * from v_cd_song_info
-> \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 19147
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: cd
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1795
Extra: Using temporary; Using filesort
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: l
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 5
ref: radiodev.cd.label_code
rows: 1
Extra:
*************************** 4. row ***************************
id: 2
select_type: DERIVED
table: s
type: ref
possible_keys: song_u_01,song_i_03
key: song_i_03
key_len: 4
ref: radiodev.cd.cd_seq_id
rows: 5
Extra:
*************************** 5. row ***************************
id: 2
select_type: DERIVED
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
5 rows in set (3.61 sec)

mysql>