Views performance in MySQL

Posted by Gianluca Bargelli on Stack Overflow See other posts from Stack Overflow or by Gianluca Bargelli
Published on 2010-03-15T15:37:11Z Indexed on 2010/03/15 15:39 UTC
Read the original article Hit count: 330

I am currently writing my truly first PHP Application and i would like to know how to project/design/implement MySQL Views properly;

In my particular case User data is spread across several tables (as a consequence of Database Normalization) and i was thinking to use a View to group data into one large table:

CREATE VIEW `Users_Merged` (
name,
surname,
email,
phone,
role
) AS (
SELECT name, surname, email, phone, 'Customer'
FROM `Customer`
)
UNION (

SELECT name, surname, email, tel, 'Admin'
FROM `Administrator`
)
UNION (

SELECT name, surname, email, tel, 'Manager'
FROM `manager`
);

This way i can use the View's data from the PHP app easily but i don't really know how much this can affect performance.

For example:

SELECT * from `Users_Merged` WHERE role = 'Admin';

Is the right way to filter view's data or should i filter BEFORE creating the view itself? (I need this to have a list of users and the functionality to filter them by role).

© Stack Overflow or respective owner

Related posts about mysql

Related posts about Performance