Re: OT: MySQL - TEXT field efficiency? Do I include in table or separate?

Replies are listed 'Best First'.
Re^2: OT: MySQL - TEXT field efficiency? Do I include in table or separate? by jhourcle (Prior) on May 30, 2005 at 12:50 UTC
A materialized view is essentially a table that's built dynamically. In Oracle, there's a command to do it directly, but I do something similar in mySQL and just rebuild the table as needed. (typically through a cron job that fires each morning, as I deal a lot in data warehouse type systems, which mysql works rather well for) In mySQL you need to specify the view directly, whereas Oracle will do query rewriting if it realizes it can. As for getting fields from two tables, yes, you just have to tell mysql how to join them. Here's an example of creating a view, (plain old view, which is basically just a way to alias the table joins and field lists) `CREATE VIEW myview AS SELECT a.field1, a.field2, a.field3, b.* FROM tablea a LEFT JOIN tableb b USING (id);` [download] I'd suggest seeing the mysql documentation for: Create View Join Syntax There is a complex set of rules about trying to update directly through a view though... depending on your type of join, the database may not be able to figure out which record it actually needs to place the value, and you can end up with odd results. For what was described in the original post, however, it should be possible to update through a view to join the bibliography data, and everything else. (because it's a one-to-one relationship, and not a one-to-many)	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: OT: MySQL - TEXT field efficiency? Do I include in table or separate?
by jhourcle (Prior) on May 30, 2005 at 12:50 UTC

A materialized view is essentially a table that's built dynamically. In Oracle, there's a command to do it directly, but I do something similar in mySQL and just rebuild the table as needed. (typically through a cron job that fires each morning, as I deal a lot in data warehouse type systems, which mysql works rather well for) In mySQL you need to specify the view directly, whereas Oracle will do query rewriting if it realizes it can.

As for getting fields from two tables, yes, you just have to tell mysql how to join them. Here's an example of creating a view, (plain old view, which is basically just a way to alias the table joins and field lists)

CREATE VIEW myview AS
SELECT a.field1, a.field2, a.field3, b.*
FROM tablea a LEFT JOIN tableb b USING (id);
[download]

I'd suggest seeing the mysql documentation for:

There is a complex set of rules about trying to update directly through a view though... depending on your type of join, the database may not be able to figure out which record it actually needs to place the value, and you can end up with odd results. For what was described in the original post, however, it should be possible to update through a view to join the bibliography data, and everything else. (because it's a one-to-one relationship, and not a one-to-many)

[reply]
[d/l]