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

Displaying posts with tag: information_schema (reset)

+0 Vote Up -0Vote Down

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the information_schema database, but it’s scattered through several different tables.

Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to …

  [Read more...]
New in MySQL 5.1: Sheeri’s Presentation
+0 Vote Up -0Vote Down

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to …

  [Read more...]
Table Sizes
+0 Vote Up -0Vote Down

During the course of my daily work I occasionally search for mysql queries which are cool and helpful. I once found the following query on

SELECT table_name article_attachment,
ROUND(data_length/1024/1024,2) total_size_mb,
ROUND(index_length/1024/1024,2) total_index_size_mb,
FROM information_schema.tables
WHERE table_schema = ‘dbname’
ORDER BY 3 desc;

A generally  lite version is:

select table_schema, table_name, (data_length)/pow(1024,2) AS ‘Data Size in Meg’, (index_length)/pow(1024,2) AS ‘Index Size in Meg’  from …

  [Read more...]
Useful database analysis queries with INFORMATION_SCHEMA
+0 Vote Up -0Vote Down

A set of useful queries on INFORMATION_SCHEMA follows. These queries can be used when approaching a new database, to learn about some of its properties, or they can be regularly used on an existing schema, so as to verify its integrity.

I will present queries for:

  • Checking on database engines and size
  • Locating duplicate and redundant indexes
  • Checking on character sets for columns and tables, looking for variances
  • Checking on processes and long queries (only with MySQL 5.1)


The following query returns the total size per engine per database. For example, it is common that in a given database, all …

  [Read more...]
Information_Schema.Partitions – table_rows
+0 Vote Up -0Vote Down

I’m in the process of converting some very large data tables to partitioned tables. By “In the process” I mean “scripts are running as we speak and I’m monitoring what’s going on.” When I did this in our test environment (2 or 3 times to be sure), I got familiar with the information_schema.partitions table. There […]

+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 …

  [Read more...]
Inspect the Query Cache using MySQL Information Schema Plug-ins
+0 Vote Up -0Vote Down

A while ago I wrote about MySQL 5.1 information schema plug-ins.

At the time, I wrote a plug-in to report the contents of the query cache, but for all kinds of reasons, I never found the time to write a decent article about it, nor to release the code.

I am not sure if I'll ever find the time to write that article, but I just tidied up the code, and installed it in a new …

  [Read more...]
Discovering FALCON Metadata in MySQL® v. 6.0.5-alpha
+0 Vote Up -0Vote Down

MySQL® 6.0.5-alpha, the latest version of the 6.x branch of the Database Server, is available for download from the SUN|MySQL Web Site.

Metadata (data about the data) are very important, especially for software developers. In this article we will see what’s new in FALCON metadata handling doing some comparison with the old 6.0.4-alpha version.

New tables in the `information_schema` database
As you know, the source for metadata is the database `information_schema`. To start, let’s see which tables related …

  [Read more...]
MySQL UC2008 presentation "Grand Tour of the information schema" now online
+0 Vote Up -0Vote Down

Yup, the presentation slides as well as the scripts are now available online on the conference website.

The stuff you will find in there:

Diagram of the information schema
Slides for the UC presentation, "Grand Tour of the Information Schema"
script, returns one row for each index (rollup of information_schema.STATISTICS)

  [Read more...]
Liveblogging: Extending MySQL by Brian ?Krow? Aker
+0 Vote Up -0Vote Down

Liveblogging: Extending MySQL by Brian “Krow” Aker

Brian wins the award for “most frequent great quotes during a talk”.

Before MySQL 5.1 a UDF was the only way to extend MySQL.

All you need in a UDF is: init() execute() deinit()

my_bool id3_parse_init(UDF_INIT *initid UDF_ARGS *args, char *message)

UDF_ARGS tell you about incoming args
char *message is the output that might return
args->arg_count is the # of args

WARNING: use STRICT mode in MySQL, otherwise there are tons of silent failures.

“When you work on databases you start to put everything …

  [Read more...]
10 Newer Entries Showing entries 61 to 70 of 74 4 Older Entries

Planet MySQL © 1995, 2015, 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.