MySQL AB announced today that its internal benchmark team has won an international contest sponsored by the renowned IT industry magazine c´t for the fastest e-commerce database application. The magazine's editors held the contest to evaluate database performance in real-world business use by creating a standard online inventory system.

Peter Zaitsev over at the excellent MySQL Performance Blog recently wrote an article on duplicated and redundant indexes – any indexes which cover exactly the same columns as another index, or cover a leftmost prefix of another index. While there are subtleties, such as FULLTEXT indexes not being the same as non-FULLTEXT, for the most part this is sufficient criteria to raise possible duplicates to a DBA’s attention. I opened my big mouth in the comments and said I could write a quick Perl script to discover possible offenders in just a few lines of code.

Well, it’s Sunday night so I will put this down to being the weekend. The background to being caught out is a request I made to my local Users Group mailing list for some information on people’s environments because I wanted to some empirical data analysis without having any more knowledge of the systems.

In summary (without the surrounding fan-fare, I was seeking):

SELECT VERSION();
SHOW STATUS;
SHOW VARIABLES;  // Optional

I was however perplexed why my first data point analysis (Read/Write ratio) using the Status values Com_insert, Com_update, Com_delete and Com_select was not always giving me expected results. In particular, a number of server results showed 0 for values while I knew the results came from working MySQL environments.

So, sanity check with good friend Morgan and I get the response to answer the dilemma SHOW STATUS defaults to session …

It's three months ago since MySQL 5.1.11 was released and many things have changed since then. Just watch the Change Log for 5.1.12 - I believe it's one of the longest ever:

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html

But I've found another nice and interesting thing: watch this bug report. This says that it's now allowed to do many more things in Prepared Statements inside Stored Procedures:

SHOW BINLOG EVENTS
SHOW (MASTER | SLAVE) STATUS
SHOW (MASTER | BINARY) LOGS
SHOW (PROCEDURE | FUNCTION) CODE (parsable only in debug builds)
SHOW CREATE (PROCEDURE | FUNCTION | EVENT | TABLE | VIEW)
SHOW (AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS)
CHANGE MASTER
RESET (MASTER | SLAVE | QUERY CACHE) …

Got up and went walking this morning. This is probably going to be one of the last few good weekends here in Seattle, and I wanted to make sure that I got in some walking each day.

Came home to discover that Christine had been stung by a Yellow Jacket. Despite the full can of Yellow Jacket killer I sprayed into the nest last night, they seem to be quite alive. I am tempted to run to the store and get some of the instant foam I use for sealing insulation leaks. I wonder how well they could get out of their nest if it was filled full of instant insulation :)

So this afternoon has turned into "fetch her some ice", so I decided to code up a small project I've been meaning to write for a few years:

mysql> INSTALL PLUGIN memcache SONAME 'libmemcache_engine.so';
Query OK, 0 rows affected, 0 warnings (0.06 sec)

mysql> create table foo3 (k varchar(128), val varchar(300)) …

After directing a Devshed poster looking for a way to compute the number of months between two dates to the manual I decided to turn the solution posted in user comments by Isaac Shepard into a function, here it is:

1. DELIMITER $$
2. DROP FUNCTION IF EXISTS `test`.`months_between` $$
3. CREATE FUNCTION `test`.`months_between` (date_start DATE, date_end DATE) RETURNS INT
4. BEGIN
5. SELECT IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) > 0, (((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) - (MID(date_end, 9, 2) < style="color: rgb(102, 204, 102);">(date_start, 9, 2)), IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) < …

Development of a more structured version is in progress. I just committed a set of changes to the repository that changes the current, rather monolithic, single-class design to what will be the first step of a more modular one.

Currently the generation of ALTER TABLE statements has been removed and the output format of the analysis is slightly different. But that is mere cosmetic.

What is much more important is that now the gathering of database schema information is based on a pluggable system, designed around an interface called IndexDescriptorProvider. Up to now I have just ported the MySQL 4 stuff that was already in the first version to this new architecture. Please feel free to have a look at it and tell me what you think.

Next thing I'll do is implement a provider based on the INFORMATION_SCHEMA database available in MySQL 5.x to see if I missed anything.

Go to …

I'm reading an interesting research paper (to be blogged later) that mentions a fact that I find pretty compelling:

In 2001 Debian included more than 55,000,000 lines of code, with an estimated (COCOMO) value of $1.9 billion.That's just Debian (and, as Wheeler notes, it grew to 230 million lines of code by 2005, putting its COCOMO price at $8 billion).

What number would we get to if we added MySQL (2M+ lines of code, I believe), JBoss, etc.? I can't even begin to come up with a rational number, but I'm guessing we're north of $50 billion. $50+ billion worth of free (as in cost and freedom) and open source software.

Priceless.

(Keep in mind that there's more to good software than code size. …

I'm reading a research paper [PDF] by Nicholas Economides (NYU) and Evangelos Katsamakas (Fordham) called "Linux vs. Windows: A comparison of application and platform innovation incentives for open source and proprietary software platforms." Long title, but the conclusion of the paper is relatively brief:

In our model, firms and developers invest to improve the quality of the platform or the application and expand the demand by users of these software products. When the operating system is proprietary, the platform provider and the application provider invest only in their own product to maximize their profit. When the operating system is open source, there is no platform provider firm, but the users invest in the platform to maximize their user surplus and their development reputation, which depends on the success of the platform measured by its …

best practices, database, internet, mysql, security, software, technology web

With recent posts by Frank Mash and Stewart Smith about password …