Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 11 1 Older Entries

Displaying posts with tag: views (reset)

Auto caching tables
+2 Vote Up -0Vote Down

Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated/invalidated?

Hint: yes.

But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have nothing changed in the query itself? No caveats, no additional WHEREs, and still have that autocache_data_table provide with the correct data, dynamically updated by some rule of our choice?

And: no crontab, no event scheduler, and no funny triggers on data_table? In such way that invalidation/revalidation occurs upon SELECT?

Well, yes.

This post is long, but I suggest

  [Read more...]
Viewing RMAN jobs status and output
+0 Vote Up -1Vote Down

Yesterday I was discussing with a fellow DBA about ways to check the status of existing and/or past RMAN jobs. Good backup scripts usually write their output to some sort of log file so, checking the output is usually a straight-forward task. However, backup jobs can be scheduled in many different ways (crontab, Grid Control, Scheduled Tasks, etc) and finding the log file may be tricky if you don’t know the environment well.
Furthermore, log files may also have already been overwritten by the next backup or simply just deleted. An alternative way of accessing that information, thus, may come handy.

Fortunately, RMAN keeps the backup metadata around for some time and it can be accessed through the database’s V$ views. Obviously, if you need this information because your database just crashed and needs to be restored, the method described here is useless.


  [Read more...]
ROUTINE_PRIVILEGES implementation
+1 Vote Up -0Vote Down

Following up on MySQL security: inconsistencies, and on MySQL bug #61596, I was thinking it may take a long time till the non-existent ROUTINE_PRIVILEGES view is implemented. Here’s my own implementation of the view.

I’ve followed the somewhat strange conventions used in the *_PRIVILEGES tables in INFORMATION_SCHEMA, where the IS_GRANTABLE is a separate column, although in 2nd 1st normal form.

I present it here as a query, using session variables, rather than a view definition:

SELECT STRAIGHT_JOIN
  CONCAT('\'', User, '\'@\'', Host, '\'') AS GRANTEE,
  NULL AS ROUTINE_CATALOG,
  Db AS ROUTINE_SCHEMA,
  Routine_name AS
  [Read more...]
MySQL: Using Views as Performance Improvement Tools
+0 Vote Up -0Vote Down
The most basic and most oft-repeated task that a DBA has to accomplish is to look at slow logs and filter out queries that are suboptimal, that consume lots of unnecessary resources and that hence slow down the database server. This post looks at why and how VIEWs can help against such suboptimal operations.
Find friends of friends using MySQL
+1 Vote Up -1Vote Down

In a previous article, I've already talked about an optimized way to connect locations in a geographic point of view by using MySQL. In this manner, locations of pubs, drugstores, barbers or even users can be obtained. Communities, or perhaps I should use the newer term Social Networks, make use of the buddy network of indiviual members in addition to the geographical mapping. This has many psychological advantages, because new members can be integrated in an established network very easily and I'm more willing to become involved when I already know some of the members.

Read the rest »

Next Week’s MySQL Sessions at ODTUG Kaleidoscope
+3 Vote Up -1Vote Down

By now you know that there is a MySQL Track during next week’s ODTUG Kaleidoscope in Washington, DC. Ronald Bradford and I organized the schedule at the last minute (Ronald did a lot of the work!). It was difficult to fill a schedule with 19 sessions that are either 1 hour or 1.5 hours long, and to do it I ended up with three presentations.

At each presentation I will be giving away a copy of The MySQL Administrator’s Bible, so be sure to show up! All MySQL track sessions are in Maryland C, and all times are Eastern.

On Monday, June 28th from 4 pm – 5:30 pm I will be presenting

  [Read more...]
Materialized view makes login process 25k times faster
+0 Vote Up -1Vote Down
It may sound like a dramatic number, and that’s because it is. One thing that is enjoyable about working on beta applications is finding new solutions and better methods to improve the user experience. The original method for displaying the recent addition of overview analytics data in the beta version of Kontrollbase was to run [...]
CCK & Views, My First Experiences
+0 Vote Up -0Vote Down

and probably my last one ..

I mentioned a couple of weeks ago that I was looking into a way of converting my static page with published papers, articles and presentations in a more dynamic page where I could create RSS feeds from the updates and feed them into another site for everybody to use.

Some people suggested that I'd have a look at CCK & Views for this. While up till now when creating an app I had usually written my own Drupal module with my own database schema and manually written SQL Queries. This indeed looked like the perfect opportunity to dig into the CCK and Views thingie.

Now I must admit that I`m not really fond of "generated queries"

I've had nightmares before

  [Read more...]
Blank VIEW_DEFINITION?
+0 Vote Up -0Vote Down

As I putter around the MySQL INFORMATION_SCHEMA, I am finding lots of undocumented behavior for fields that should be straightforward. For example, the VIEWS table holds information about views, and the VIEW_DEFINITION field contains the view definition, right?

Well, when I was looking at the VIEW_DEFINITION today, I noticed an odd thing. Even though I had permissions to see the view definition (as proven by the SHOW CREATE VIEW command), the INFORMATION_SCHEMA.VIEWS table sometimes came up blank for the VIEW_DEFINITION. I had to figure out why, and now that I know, I’m not sure if it’s a bug or a feature…..can you figure it out?

mysql> USE INFORMATION_SCHEMA;
Database changed
mysql> SELECT TABLE_NAME,VIEW_DEFINITION FROM VIEWS WHERE TABLE_SCHEMA='sakila';
  [Read more...]
Materialized Views for MySQL
+0 Vote Up -0Vote Down
I just read about OLAP4ALL's Materialized Views for MySQL software that "offers the functionality of Materialized Views in MySQL that are not natively supported in the MySQL database.".

What nags me about this is the fact that it is "implemented as a separate Java program running on the server where MySQL is installed".

The server-side of this should be doable using Stored Procedures and/or Triggers. For the client-side a bit of convenience functionality for query rewriting would be needed.

Sounds like an interesting project that I would like to pursue when I find the time.









  [Read more...]
Showing entries 1 to 10 of 11 1 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.