As part of my job at Pentaho, I occasionally get to help build solutions using the Pentaho BI platform and the solution building toolset, Design Studio, Report Wizard and Designer. I really enjoy that time, in part because I miss my developer days, but also because using the software we build makes me feel like I can connect and relate more to our community. The end product is I can do my job better, because I know the project and the software better.

I recently started creating some reports against our case tracking data, which lives in a JIRA database. JIRA is a phenomenal issue tracker, but I just can't get the reports and data analysis out of it that I really need. And that's OK, that's what we have the Pentaho platform for. One thing I came across that I thought would be good to explain a bit is the difference between "Save As" and "Publish" in our report …

Anyone who has heard me speak or give a webinar knows I repeatedly harp on a single concept that comes up time and time again when discussing software architecture, performance tuning, database development, or really anything technical. That is, there are few real absolutes in the art of software development.

I know that one of the most annoying answers to any question is "it depends". But, unfortunately, this is an accurate answer for many questions that have the form "What is the best way to do...".

A question that was asked during Tuesday's webinar on Tagging and Folksonomy schema choices was the following:

Could you tell us the best practices for managing summary or statistic data in the database?

I responded online that I would write a blog entry with answers to that question, and I that blog article is currently underway and should be available soon. However, I want to point out that there really …

So, recently, I've been spread out working on quite a few projects and trying to get a bunch of community members talking with each other and collaborating (or at least starting to talk about efforts to contribute to the MySQL code base). Some of the main things I've been working on are the following fairly big projects, all of which I am still looking for suggestions, volunteers, and input from everyone.

The Community Build Farm

About a month ago, I broached the topic of having a community build and testing platform through which community members can enlist any spare boxen lying around their houses in order to pull the most recent snapshots from our source trees and automatically build and test those snapshots.

Ronald Bradford, our community extraordinaire down-under, put together …

It's already a month old, but I just stumbled over this ITtoolbox Interview with Jonathan Cheyer (Open Source Community Manager) and Murat Demiroglu (Senior Product Manager) from Solid Information Technology, in which Dru Lavigne talks with them about Solid (the company) and the solidDB for MySQL storage engine, which is currently in beta testing. I still remember Solid from my times at SUSE Linux, where it used to be a part of the Linux distribution along with MySQL, PostgreSQL, Adabas D and several other DBs...

It's already a month old, but I just stumbled over this ITtoolbox Interview with Jonathan Cheyer (Open Source Community Manager) and Murat Demiroglu (Senior Product Manager) from Solid Information Technology, in which Dru Lavigne talks with them about Solid (the company) and the solidDB for MySQL storage engine, which is currently in beta testing. I still remember Solid from my times at SUSE Linux, where it used to be a part of the Linux distribution along with MySQL, PostgreSQL, Adabas D and several other DBs...

Rasmus Lerdorf, creator of PHP, gives an interesting interview on FLOSS Weekly, Chris DiBona & Leo Laporte's open source podcast.  He talks about the origins of PHP, the challenge of letting go of control and how he helped make the development process more open.  This is a challenge for all open source projects (MySQL included) and there are good lessons to be learned here.

You can listen to this from the web or subscribe through iTunes.

• TWIT: Floss Weekly 12, MP3
…

I got home today and sat down to read my home email list. Nothing new. But on a MySQL mailing list, there was an enquiry why performance was slowing in a given application. I didn’t even have to read the situation, nor the problem, it took less then the 200ms mentioned to identify the problem looking at the supplied schema.

In summary, the first table in the schema had a primary key of VARBINARY(255) and a engine type of Innodb. Hold on, wait, it’s a concatenated key of two VARBINARY(255) columns. And I should mention, that primary key was a foreign key in the next table. If this was a home website app with one user, ok well it’s still bad, but this application was having performance problems with reasonable volumes of transactions, it’s not a beginner application. (A recent reference If you don?t know your data, you don?t know your application). Where do people learn …

If you’re interested in looking at what goes into the MySQL documentation, there’s a new and kind of cool gizmo we’ve just installed that makes browsing the docs sources a breeze. Fisheye lets you browse by project, directory, author, date, and other criteria. It also provides an easy way to get to the complete changelogs, and even provides a customisable changelog RSS feed — for example, this feed has commits for just the NDB API documentation, and this is a feed of (all) my commits to the mysqldoc repository.

The …

Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause.
In MySQL 5.0 support for views was added.

These features are quite related to each other but how do they compare in terms of performance ?

Derived Tables in MySQL 5.0 seems to have different implementation from views, even though I would expect code base to be merged as it is quite the same task in terms of query optimization.

Derived Tables are still handled by materializing them in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example).

One more thing to watch for is the fact derived table is going to be materialized even to execute EXPLAIN statement. So if you have done mistake in select in from clause, ie forgotten join condition you might have EXPLAIN running forever.

Views on other hand …

If you will try to compile nginx in Redhat Linux, you can get following error even if you have pcre-devel package installed:

Configuration summary + threads are not used + PCRE library is not found .... .... ./configure: error: the HTTP rewrite module requires the PCRE library. You can either disable the module by using --without-http_rewrite_module option, or install the PCRE library into the system, or build the PCRE library statically from the source with nginx by using --with-pcre= option.

If you are experiencing this small problem, you should simply use following parameter of configure script:

# ./configure --with-cc-opt="-I /usr/include/pcre"

And voila!

+ using system PCRE library

Now it can see and will use system PCRE library.