Path 1 (Click Here For SQL Analysis)

CS290F Fall 2006 - UCSB Computer Science - Thorsten von Eicken

Jump to: navigation, search

Contents

[edit]

Homepage

SHOW FIELDS FROM musics�
SELECT count(*) AS count_all FROM musics
SELECT * FROM musics LIMIT 0, 10
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1) 
SELECT * FROM ratings WHERE (ratings.music_id = 1)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2) 
SELECT * FROM ratings WHERE (ratings.music_id = 2)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3) 
SELECT * FROM ratings WHERE (ratings.music_id = 3)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4) 
SELECT * FROM ratings WHERE (ratings.music_id = 4)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5) 
SELECT * FROM ratings WHERE (ratings.music_id = 5)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6) 
SELECT * FROM ratings WHERE (ratings.music_id = 6)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7) 
SELECT * FROM ratings WHERE (ratings.music_id = 7)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8) 
SELECT * FROM ratings WHERE (ratings.music_id = 8)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9) 
SELECT * FROM ratings WHERE (ratings.music_id = 9) 
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10)
SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1
[edit]

Search For Bob Marley by Artist Name

SHOW FIELDS FROM musics
SELECT count(*) AS count_all FROM musics WHERE (artist like '%Bob Marley%')

EXPLAIN SELECT count(*) AS count_all FROM musics WHERE (artist like '%Bob Marley%');
Before:
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | musics | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using where | 
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
After adding index for artist:
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | musics | index | NULL          | artist_index | 258     | NULL |   12 | Using where; Using index | 
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
[edit]

View results

SELECT * FROM musics WHERE (artist like '%Bob Marley%') LIMIT 0, 10
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 12)

EXPLAIN SELECT * FROM musics WHERE (artist like '%Bob Marley%') LIMIT 0, 10
Before:
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | musics | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using where | 
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
 
After adding index for artist:
EXPLAIN SELECT * FROM musics WHERE (artist like '%Bob Marley%') LIMIT 0, 10;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | musics | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using where | 
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

NOTE: Apparently adding index only helps when select query used with exect matches... ex:
EXPLAIN SELECT * FROM musics WHERE (artist='Bob Marley') LIMIT 0, 10;
+----+-------------+--------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | musics | ref  | artist_index  | artist_index | 258     | const |    1 | Using where | 
+----+-------------+--------+------+---------------+--------------+---------+-------+------+-------------+
[edit]

Listen to Song

NO SQL queries necessary
[edit]

Login

SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1
[edit]

Rates Song

SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1
SELECT * FROM ratings WHERE (music_id=12 and user_id=9) LIMIT 1
SHOW FIELDS FROM ratings
SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1
SELECT * FROM ratings WHERE (music_id=12 and user_id=9) LIMIT 1
SHOW FIELDS FROM ratings
BEGIN
INSERT INTO ratings (`score`, `review`, `user_id`, `music_id`) VALUES(5, 'great song... a classic', 9, 12)
COMMIT
[edit]

User gets redirected back to the search result:

SHOW FIELDS FROM musics
SELECT count(*) AS count_all FROM musics WHERE (artist like '%Bob Marley%')
SELECT * FROM musics WHERE (artist like '%Bob Marley%') LIMIT 0, 10

For SQL EXPLAIN, see #Search For Bob Marley by Artist Name
[edit]

Rendering store/index

SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 12)
SELECT * FROM ratings WHERE (ratings.music_id = 12)
SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1
[edit]

Add to Playlist (cart)

SELECT * FROM musics WHERE (musics.id = '12') LIMIT 1
SHOW FIELDS FROM musics
[edit]

User gets redirected back to the search result:

SHOW FIELDS FROM musics
SELECT count(*) AS count_all FROM musics WHERE (artist like '%Bob Marley%')
SELECT * FROM musics WHERE (artist like '%Bob Marley%') LIMIT 0, 10
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 12)
SELECT * FROM ratings WHERE (ratings.music_id = 12)
SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1

For SQL EXPLAIN, see #Search For Bob Marley by Artist Name
[edit]

Checkout

SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1
SHOW FIELDS FROM orders
SHOW FIELDS FROM musics
[edit]

Place Order

[edit]

User places order:

SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1
SHOW FIELDS FROM orders
SHOW FIELDS FROM line_items
SHOW FIELDS FROM musics
BEGIN
COMMIT
BEGIN
INSERT INTO orders (`city`, `name`, `name_cd`, `zipcode`, `cc_cvv`, `pay_type`, `street_address`, `cc_num`, `cc_exp`, `user_id`, `email`, `state`)
VALUES('938', 'Best Song', 'Bob\'s song', '93117', '345', 'cc', '239', '445', '345', 9, 'bob@bob.com', 'CA')
INSERT INTO line_items (`order_id`, `total_price`, `quantity`, `music_id`) VALUES(10, 0.6, 1, 12)
COMMIT
[edit]

User gets redirected back to the index page:

SHOW FIELDS FROM musics
SELECT count(*) AS count_all FROM musics
SELECT * FROM musics LIMIT 0, 10
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1)
SELECT * FROM ratings WHERE (ratings.music_id = 1)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2)
SELECT * FROM ratings WHERE (ratings.music_id = 2)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3)
SELECT * FROM ratings WHERE (ratings.music_id = 3)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4)
SELECT * FROM ratings WHERE (ratings.music_id = 4)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5)
SELECT * FROM ratings WHERE (ratings.music_id = 5)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6)
SELECT * FROM ratings WHERE (ratings.music_id = 6)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7)
SELECT * FROM ratings WHERE (ratings.music_id = 7)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8)
SELECT * FROM ratings WHERE (ratings.music_id = 8)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9)
SELECT * FROM ratings WHERE (ratings.music_id = 9)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10) 
SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1
[edit]

Logout

[edit]

User logs out:

SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 9 ) LIMIT 1
BEGIN
UPDATE users SET `created_at` = '2006-10-29 14:55:35', `login` = 'admin', `crypted_password` = 'c234257313a861411cb56631ec771dd26c70b975',  
`remember_token_expires_at` = NULL, `salt` = '397c58c23f658b371a9cdbc495e8f15fb7deb6fd', `remember_token` = NULL, `email` = 'admin', 
`updated_at` =  '2006-11-06 20:01:16' WHERE id = 9
COMMIT
[edit]

After logging out, user gets redirected back to the index page:

SHOW FIELDS FROM musics
SELECT count(*) AS count_all FROM musics
SELECT * FROM musics LIMIT 0, 10
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1)
SELECT * FROM ratings WHERE (ratings.music_id = 1)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2)
SELECT * FROM ratings WHERE (ratings.music_id = 2)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3)
SELECT * FROM ratings WHERE (ratings.music_id = 3)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4)
SELECT * FROM ratings WHERE (ratings.music_id = 4)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5)
SELECT * FROM ratings WHERE (ratings.music_id = 5)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6)
SELECT * FROM ratings WHERE (ratings.music_id = 6)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7)
SELECT * FROM ratings WHERE (ratings.music_id = 7)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8)
SELECT * FROM ratings WHERE (ratings.music_id = 8)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9)
SELECT * FROM ratings WHERE (ratings.music_id = 9)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10)
Retrieved from "http://www.voneicken.com/courses/ucsb-cs290f-fa06/index.php/Path_1_%28Click_Here_For_SQL_Analysis%29"