Views In 5.01

Converting MSSQL Views To MySQL Version 5 Views
I want to convert my mssql database and view into Mysql version 5. I was successful to convert tables using couple of scripts out there but the views are never supported. Anyone have a tool I could get to convert my views.

---

Views?
do views exist fysically a separate "table",
or are they generated on the fly whenever they are queried?

if they exist fysically they could improve performance (..php, web),
for example a view being a join between two or more tables..

---

Views?
do views exist fysically a separate "table",or are they generated on the fly whenever they are queried?

if they exist fysically they could improve performance (..php, web),for example a view being a join between two or more tables..

---

Views In MySQL 5.x
Was wondering if anyone here has had any experience with views in mysql 5? I know what they are and how to use them, was just wondering if anyone here has before and if they feel it is better, in mysql, to just create the new table or use the view instead? Or perhaps a complex join?

---

Question About VIEWS
I have never used MySQL views before, however, in my current situation I will sooner or later need to use them.

I have one table which consists of sent messages and another which consists of recieved messages. When someone sends a message to another person, the message will be stored in their own sent messages. However, when they recieve a reply from the end user, the message will appear as a conversation as apart of their own inbox. So basically, the query which requests the inbox records will fetch both the sent and recieved message records.

So I need to use a view to fetch both messages, how do I do this?

If you guys are still wondering what I am talking about, then check out FaceBook's private messaging system.

---

Views Overhead
Not far long ago, I was posting a question about performance of database with a lot of tables. Now I have a good idea towards moving to a shared tables approach, so there will be only one set of tables for all users, but each of the users will have an own set of views like:

create view username_posts as
select id, title
from posts
where user_id = user_id
It would make database more efficient, and would need only a small set of changes to the application.

What do you think about this approach? Is there a huge overhead of using views like the one mentioned above, in comparison with adding the user_id = ... clause to the application? How well does MySQL (InnoDB) behave in case database has a lot of views?

---

As Views Really Supported
With MySQL 5 beta and InnoDB tables, are views really supported? I can see the syntax in the manual but I get syntax errors on even a trivial attempt to define a view as in

create view foo as select * from project

produces You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'view foo as select * from project' at line 1

---

Phpmyadmin And Views
I created a veiw in phpmyadmin How can I modify it? when i click on structure instead of seeing the view I see it as if it was a table with fields.

---

Views In MySQL
Can I create views in MYSQL. Or is there an alternate technique.

---

Views In MySQL
I want to migrate my existing SQL Server database to MySQL. It had many views in it. When i tried to make views in MySQL 5.0, it gives error. I was suggested by this forum that views can be created in MySQL version 5.0.1, but i couldn't download it from anywhere. From where can i get it?

I tried to download from mysql.com file named "mysql-5.0.1-alpha-snapshot-win.zip".
This downloaded file gives me 5.0.0-alpha-nt server and 5.0.1-alpha client. This version is also not allowing me to create a view.

---

Views, Procedures
I have version 4.0.12 of MySql installed. I can not create views and stored procedures. Is this normal or is there something wrong in the installation?

---

Performance Of Views
I have a large table in a database that takes a long time to do any queries on. All user queries on this table are done as part of a background process at the moment.

The table holds transaction information from organisation and account id's; each organisation may have more than one account id. This table contains the raw transaction data, so there are many 1000's of records for each id. The table is keyed on customer number, which is different from organisation id and one customer number may have more than one organisation id. Code:

---

Views In MySql/PHP
This thread is answering a question from [url=http://www.codingforums.com/showthread.php?s=&threadid=12528[/url]this thread] -- I didn't want to distract from the original question.

To recap:A view is a virtual table. It is a stored query, usually based on two or more tables. It can be used as a table by other queries (by default, all queries created in Access are, in fact, views).

The main advantages are efficiency and security. You can merge complex tables that store complete information to produce more simple tables that contain live information without any data duplication. You can also give the view query different access, meaning that you can let people see only some columns of a table (eg a view restrict who can see creditcard numbers, without restricting who can see other customer details).

They are not supported in mySql.
Followed by:
what is the alternative to 'view' in mySQL/PHP then ...
The simple answer is there is no alternative. You can easily enough create a database layer that stores queries in php. But, if the user has access to run these queries on the underlying tables, then they also have access to bypass them.

You can also, with a little effort, create a method for building queries from SQL (this is a fairly standard way of operating a large system). However, you do not get the dabase level security benefits that you get from using views. Nor do you get the performance benefits that you get from views (I don't know all the details, but a view does run faster than a regular sql query).

You can use a degree of database level security -- by limiting access to tables using GRANT statements. With views, you can use database security to completely limit access to rows, columns, and calculated fields without any data duplication. Of course, it is possible to implement application level securty in PHP, but the dual (or more often treble) layer of security is quite useful when you're dealing with complex two and three tier systems.

---

Phpmyadmin And Views
When we create *.sql for out database, does it not copy views. I created *.sql and imported it to phpmyadmin, but it is not showing the view.

Do I have to re-write the view or there is some other solution?

---

Views In MySQL 4.1
Is it possible to create views in MySQL 4.1 or am I just wasting my time trying to figure out the syntax?

I've tried searching this site but can't find anything about it for 4.1.

---

Delete Views
how to drop a view in Ms Access using ColdFusion.I don't think the problem is ColdFusion, i think the problem is the query but it might be anything.

---

Views And MyODBC
well i just installed MySQL "5.0.1-alpha-nt-max" to play around with the new Features on my local PC.reating and accessing Views seems to work, but as it seems MyODBC 3.51.09 cant handle Views.
Are there any plans to support Views also via MyODBC ?
If so, is that Feature currently in the Bitkeeper-Repository of "MySQL Connector/ODBC 3.52" ?

---

Mysql Views
I have only ever developed database application using Mysql and so have limited experience of views and how they work. I'm sorry if this seems basic.
Suppose I create a view from a table, say A and tranform it using a select query to create my view, B.
If I want all the data from B, ie. SELECT * FROM B will the DBMS need to go back to the original data and execute the query that generated B and then return my results - or - does it store the data for view B in a table like format and then just quickly return that.
Essentially is there a performance advantage when using a view where the transformation from table A to view B is complex.

---

How To Overcome On Views Limits
I have 6 tables with many many columns and I have a SELECT statement that joins all the table and brings me all the columns in each table in single view. The number of columns that I get by executing this SELECT statement are more than 2000. The SELECT statement runs perfect and give me no problem. But the problem comes when I use this select statement in a view. I get the error message

CREATE VIEW failed because column '' in view '' exceeds the maximum of 1024 columns

Is there a way I can get all the columns in one single view. I know SQL Server View has maximum limit of 1024 colums.

---

Stored Procedure Vs Views
I am a little confused on the benefits of views when compared to stored procedures. When should you use a view and when should you use a stored procedure? Do either technology have a performance edge over the other?

---

Concept - Replacement For Views?
I'm kind of new to MySQL but am familiar with other DBs. Currently I'm working with a MySQL4 database and I have a very specific problem which could be easily solved via views.

Problem:
I have as follows -
tbl_cat
-id_cat
-txt_name
...

tbl_prop
-id_prop
-id_cat (fk)
-txt_desc
-txt_type
-txt_default
....

tbl_product
id_prod
txt_ref_name
...

tbl_prod_prop
id_prod_prop
id_prop
txt_hash


OK what is all of this? it's a dynamic product for a shop. How does it work conceptually? We create a product category(tbl_cat) and add properties to it (tbl_prop) such as name, picture, html text, etc...
Finally when we create a product, we determine it's category and fill in each of the properties.
My original idea was to use a view to generate the listing of the products and properties, but mysql4 doesn't have views...
What would be the best aproach? I thought of creating a cache table, where when a product is updated or inserted, I run a script that creates updates a record of a temporary dinamic table. Soething like a false view, updated on demand. Is that as stupid as it sounds?

---

Views Support In 5.0.0-alpha
from 1.8.5.6 Views:

"Views are currently being implemented, and will appear in the 5.0 or
5.1 version of MySQL Server."

i see the stored functions/procedures being added to the 5.0.0-alpha
release but i do not see any support for views still. are they going
to appear later in 5.0 or we would have to wait till 5.1 ?

---

Performance Improvement With Views?
If we have an option of using view to static assignment of options which one should we go for? Like if we have a combo box which is gonna have lets say 5 options then should we assign these options statically or should we store them in a view and then retrieve them dynamically each time someone clicks on the drop box.

---

Keeping Number Of Views To Max. How?
I've got a "smallint(3)" to track the number of views of an article, it is updated width "SET views=(views+1)". What I'm just wondering is if when the article reaches 999 views, the next view will set it to 100(0) or will just remain with 999. I think it will go on counting so, how can I just leave it with 999 views?

---

Version Support Views
My Question is that which version of MySQL supports Views.

---

Indices Created For Views
I have a view that combines data from 4 tables.  Can I create an index for the view?  or should the indices created for the original tables speed up searching enough?

---

Design Strategy And Views
I am writing a php interface to access a large (100 million + records) database which is broken into 50+ tables by state.The script creates a temporary merge table with a union of the selected states depending on user input.  I believe this will provide the largest reduction of unnecessary row scans.
I then query the temporary merge table with any additional criteria given by the user.
Is there a benefit if I create a view on the merge table to futher reduce unwanted rows and then query the view instead of the merge table directly?  (I'm not really clear on how a view works.  Does it actualy run the select query like a subquery?

---

Does MySql 5.1 Supports VIEWS With WHERE Clause?
Can I write "SELECT * FROM vView WHERE ..." ?

---

VIEWS,CHECK SYNTAX AND FOREIGN KEY
i am trying to see what views i have created on my database and am lost with the code part. this is what i have tried in both the USE DATABASE, USE INFORMATION_SCHEMA

SHOW VIEWS;
SHOW CREATE VIEW;
SHOW CREATE VIEWS;

ANYONE GOT ANY HELP?

I am also trying to use check and it doesn't seem to work. I am trying to 'ensure the only values entered into the GENRE table are dance, pop and jazz.
I had been trying with this:
CHECK(GENRE IN('DANCE','POP','JAZZ')

how do i add ORDER_NUM as a foreign key in a table called ORDER_LINE?
i tried this:
ALTER TABLE ORDER_LINE
ASS FOREIGN KEY(ORDER_NUM) REFERENCES CUSTOMER;

---

Views And Altering Base Tables
I want a view that contains all the colums of the base table but only the rows where the index "owner" is a certain value. Easy enough
CREATE VIEW bt_42 AS SELECT * FROM bt WHERE owner=42

This seems to me to be easier, quicker, and more maintainable than enumerating each column and it seems to work. Now the question is if I happen to add another column to bt will that automatically be reflected in all views that were made by SELECT * ? Seems like it should, since updates to other columns of bt would be reflected in bt_42

---

Does MySQL Support VIEWS, Stored Procedures?
Two questions:

(1) Does MySQL support VIEWS?
(2) Does MySQL support STORED PROCEDURES?

---

Should Page Views Count Be Stored With Content?
I am writing a db that has a table with advertisement data.  I want to keep track of how many views an ad has.

Is it bad practice to put a field "viewCount" in the same table as the content?  The alternative is to have a lookup table?

The logic behind the question is that it seems that any time you write to a table there is a possibility of something going wrong, so better to leave the content alone and update a lookup table often.

Or am I being over-cautious? Code:

---

Using Views In A Fairly High Demant Environment
I am making a forum type system in which the only interface to the database is through stored procedures. Thats not the point though.

I have run into a minor problem with my system that could be rectified if my request for the threads in a forum was drawn from a "view" table. Would a view be quite a burden on the systems performance if used in a situation like that?

---

Keeping Track Of Post Views Every Day (Table Structure)
I was thinking of having a table to keep track of post count every day.

For example, i have the following table structure:

CREATE TABLE `wp_views` (
`post_id` bigint(20) NOT NULL,
`year` tinyint(4) NOT NULL,
`month` tinyint(2) NOT NULL,
`month_hits` int(7) NOT NULL default Ɔ',
`day_1` int(6) NOT NULL default Ɔ',
`day_2` int(6) NOT NULL default Ɔ',
`day_3` int(6) NOT NULL default Ɔ',
`day_4` int(6) NOT NULL default Ɔ',
`day_5` int(6) NOT NULL default Ɔ',
`day_6` int(6) NOT NULL default Ɔ',
`day_7` int(6) NOT NULL default Ɔ',
`day_8` int(6) NOT NULL default Ɔ',
`day_9` int(6) NOT NULL default Ɔ',
`day_10` int(6) NOT NULL default Ɔ',
`day_11` int(6) NOT NULL default Ɔ',
`day_12` int(6) NOT NULL default Ɔ',
`day_13` int(6) NOT NULL default Ɔ',
`day_14` int(6) NOT NULL default Ɔ',
`day_15` int(6) NOT NULL default Ɔ',
`day_16` int(6) NOT NULL default Ɔ',
`day_17` int(6) NOT NULL default Ɔ',
`day_18` int(6) NOT NULL default Ɔ',
`day_19` int(6) NOT NULL default Ɔ',
`day_20` int(6) NOT NULL default Ɔ',
`day_21` int(6) NOT NULL default Ɔ',
`day_22` int(6) NOT NULL default Ɔ',
`day_23` int(6) NOT NULL default Ɔ',
`day_24` int(6) NOT NULL default Ɔ',
`day_25` int(6) NOT NULL default Ɔ',
`day_26` int(6) NOT NULL default Ɔ',
`day_27` int(6) NOT NULL default Ɔ',
`day_28` int(6) NOT NULL default Ɔ',
`day_29` int(6) NOT NULL default Ɔ',
`day_30` int(6) NOT NULL default Ɔ',
`day_31` int(6) NOT NULL default Ɔ',
UNIQUE KEY `post_id` (`post_id`),
UNIQUE KEY `year` (`year`),
UNIQUE KEY `month` (`month`)
)
Let say, today is the 20th July 2007, so my insert query will be like


INSERT INTO wp_views (post_id, year, month, month_hits) VALUES (1, 2007, 7, 1) ON DUPLICATE KEY UPDATE month_hits += 1, day_20 +=1;
Is my table structure alright? or is there a more efficient way of doing this?

---