Description:
I have a query which joins four tables. When I explain the query directly (SELECT a, b, c, d FROM tab1, tab2...), the time is very good. When I explain the query through the view (SELECT * FROM view), the time goes up to 4 seconds. The query plans on each are fairly similar, except that the view's plan uses DERIVED instead of the four SIMPLE's I get with the query directly. I have tried multiple ways of writing the JOINs, I have explicitly specified an algorithm, I have different table types (MyISAM and InnoDB), I have created extra indexes and foreign keys. No substantial difference occurs despite these attempts.
With most databases, my experience has been that views perform like the underlying query because the query parser is just inserting the SQL of the view (especially when you just selecting from the view). However, that doesn't seem to be the case here. I am trying to convert an application from Oracle to MySQL which relies on views, so this is very important.
This evening I've tried the same thing on PostgreSQL and do not experience the difference. It really appears that there is some problem with the view, where it looks through a lot more rows than it has to. There was another bug dealing with performance, but it was 5.0.7 and was on Linux, so I thought it was worth reporting separately.
The explain plan for query vs. view:
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,'<','<') 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,'<','<') 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>
How to repeat:
Really as simple as taking a four table join and creating a view out of it. The tables do not have to be insanely large, the song table in my case has around 19,000 rows, which really isn't that many.
