OurSQL Episode 142: Great Views

This week we discuss MySQL Views. Ear Candy is GRANTs and replication and At the Movies is a presentation by Giuseppe Maxia talking about "Creating Multi-Master clusters with Tungsten Replicator".

Events
DB Hangops - every other Wednesay at noon Pacific time

Upcoming MySQL events

Training
SkySQL Trainings

Tungsten University trainings

Oracle's MySQL Trainings

Percona Trainings

Views
Simplest CREATE VIEW statement:
CREATE VIEW viewname AS SELECT_statement;

MySQL Views Manual page

For example:
CREATE VIEW EmailInfo AS SELECT name,email FROM InfoTable;

SQL SECURITY INVOKER example:

CREATE 
SQL SECURITY INVOKER
VIEW EmailInfo AS SELECT name,email FROM InfoTable;

Row-based permissions with a view:

CREATE VIEW EmailInfo AS
SELECT name, email FROM InfoTable WHERE email IS NOT NULL;

Different definer example:

CREATE DEFINER=nonSuperUser@localhost
SQL SECURITY DEFINER
VIEW EmailInfo AS SELECT name,email FROM InfoTable;

Aggregate view:

CREATE VIEW rentals_per_day AS
SELECT COUNT(*), DATE(rental_date) AS business_day
FROM sakila.rental
GROUP BY business_day;

SELECT * FROM rentals_per_day WHERE business_day='2005-08-01';
Looks like:

mysql> EXPLAIN SELECT * FROM rentals_per_day WHERE business_day='2005-08-01'\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 41
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: rental
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 15596
Extra: Using temporary; Using filesort
2 rows in set (0.05 sec)

Given the EXPLAIN we can deduce that the SELECT query was really turned into:

SELECT * FROM
(SELECT COUNT(*), DATE(rental_date) AS business_day
FROM sakila.rental
GROUP BY business_day) AS view_defn
WHERE business_day='2005-08-01';

Better way to do the query, but not possible with views:

SELECT COUNT(*)
FROM rental
WHERE rental_date between '2005-08-01 00:00:00'
AND '2005-08-01 23:59:59';

VIEW algorithms - TEMPTABLE and MERGE

views and replication

updatabase views and WITH CHECK OPTION.

WITH CHECK OPTION example:

CREATE VIEW EmailInfo AS
  SELECT name,email FROM InfoTable WHERE email IS NULL
WITH CHECK OPTION;

Ear Candy
GRANTs and replication

At the Movies
This week in At the Movies, we present Giuseppe Maxia of Continuent talking about Creating Multi-Master clusters with Tungsten Replicator. This was presented at Open Database Camp in Boston 2 months ago.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1247