Some people think that normalization is the ultimate goal of databases. Pure bunkum. Your data should be structured to make typical queries tractable. A typical example is search engines. Normalization isn't sufficient if access time sucks.

Admittedly, search engines are a relatively trivial case. Regardless, query time contributes to user satisfaction and insertion time contributes to search coverage. If your schema handles both with ease then you're done. To achieve this, your schema will be normalized, or at the very least, strategically de-normalized.

Trees

This is very good but sometimes the simplest and most reasonable requests leave me stumped:

I want to do a simple query that displays categories and all it's nested subcategories in order. ... Is it possible to do this with one query or must I make a loop to get them all?

-- …

Expect a longer post in the near future on upgrade procedures. For now enjoy this quote from a gentoo user illustrating the worst way to upgrade.

linolium: is there a way to wipe every single table and start over from scratch?
linolium: ( the upgrade from mysql 4 to 5 didn’t go as smoothly as planned
me: did you read the upgrade notes?
linolium: no, I just hoped that portage would be kind enough to do those things for me

I’m a keen advocate of MySQL. However, while I use it and promote it within my limited IT circles, I often wonder how MySQL can get better traction and exposure, especially within both the industry sectors and physical locations where I am presently.

This presents a dilemma, it’s almost like the term that has been used in Venture Capital, and in the well named book, Crossing the Chasm. I see and believe that MySQL already has good penetration within certain industry sectors, specifically Internet Based, Startup Based, or Small Based segments. However, I’m sure within other commercial sectors, MySQL has either a token exposure or little to no exposure at all, at least in the circles I mix with.

So how can MySQL the product and MySQL AB the company get both better exposure and …

I’ve started now to actively use more of the MySQL Workbench, MySQL Administrator and MySQL Query Browser and MySQL Migration Toolkit. I am traditionally a very command line person, and it’s important that these skills are never put on the back burner. For all those budding Developers and DBA’s you need these skills, expecially for any MySQL Certification.

To indicate my indent, I’ve even created a new blog category specifically for MySQL GUI Products.
As I’ve mentioned previously, Mike Zinner from MySQL AB really …

Time to Upgrade my WordPress Blog software from Version 1.5.2 to Version 2.0.2 after my latest spam attacks and Combating Blog Spam attempt.

Here is what I did.

cd /home/arabx/www
tar cvfz blog.backup.20060520.tar.gz wordpress-1.5.2
mysqldump -uroot -p arabx_blog > blog.db.20060520.sql
mv blog.*20060520* /u01/backup   # Not in my WWW
scp blog.*20060520* to@someplacesave:/backup
wget http://wordpress.org/latest.tar.gz
mv latest.tar.gz    wordpress-2.0.2.tar.gz # I really hate unversioned files

Disable Plugins. You had to do this manually from the Admin interface (I’m sure it’s just a SQL statement).
Goto http://blog.arabx.com.au/wp-admin/plugins.php

Now some more work.

tar xvfz wordpress-2.0.2.tar.gz  # creates a wordpress directory
mv wordpress wordpress-2.0.2
ln -s wordpress-2.0.2 blog
cp wordpress-1.5.2/wp-config.php wordpress-2.0.2 …

So, I try to answer questions on the mysql users list. It usually frustrates me, but I also want to help. And often, I learn things.

My 2nd biggest pet peeve is that people don’t think to Google their findings. Or even “MySQL” their findings. If you have a short phrase or word, type the following into your address bar and MySQL does the right thing:

http://www.mysql.com/short word or phrase

try it:

http://www.mysql.com/replication
http://www.mysql.com/insert syntax
http://www.mysql.com/can’t%20connect

The bigger pet peeve I have is that people are AFRAID. They shake in their boots if there’s something they don’t understand. This is why there are such things as test servers. Most …

Returning to post Group commit and real fsync I made several experiments:

I ran sysbench update_key benchmarks without ---log-bin, with ---log-bin, and with ---log-bin and ---innodb-support-xa=0 (default value is 1). Results (in transactions / sec)

Scott recently IMed me and asked how to find out which blogging categories are his most commented, and I thought I’d share the query:

SELECT wp_categories.cat_name, COUNT(wp_comments.comment_id)
FROM wp_categories LEFT JOIN wp_post2cat ON wp_categories.cat_id = wp_post2cat.category_id
LEFT JOIN wp_comments ON wp_post2cat.post_id = wp_comments.comment_post_id
WHERE comment_approved != ’spam’
GROUP BY wp_categories.cat_id
ORDER BY 2 DESC

+------------------------------------------+-------------------------------+
| cat_name                                 | COUNT(wp_comments.comment_id) |
+------------------------------------------+-------------------------------+
| MySQL                                    |                           307 |
| Visual Basic 6                           |                           236 |
| General …

I was following conversation on LogAnalysis mailing list (LogAnalysis@lists.shmoo.com) and one of the members suggested that finding out if a web site is defaced is one of the valuable things to find out.

Realizing, that all my hosted web sites are stored in a MySQL table (for automated management) — what would it take to add the functionality?

Just add one column for the main page "page TEXT" (and a flag "verify CHAR(1)" in case you don't want checking on a domain)... and add 50 lines of Perl...

!/usr/bin/perl

# NOTE: Your wget needs to be at least v1.8 to handle PHP based pages
# NOTE: Could use Algorithm::Diff -- if it was more widely available

use strict;
use DBI();

my $debug = 0;

# Connect to sites database
my $dbh = DBI->connect("DBI:mysql:database=floyd;host=localhost", "USERNAME", "PASSWORD", {'RaiseError' => …

I was following conversation on LogAnalysis mailing list (LogAnalysis@lists.shmoo.com) and one of the members suggested that finding out if a web site is defaced is one of the valuable things to find out.

Realizing, that all my hosted web sites are stored in a MySQL table (for automated management) — what would it take to add the functionality?

Just add one column for the main page "page TEXT" (and a flag "verify CHAR(1)" in case you don't want checking on a domain)... and add 50 lines of Perl...

!/usr/bin/perl

# NOTE: Your wget needs to be at least v1.8 to handle PHP based pages
# NOTE: Could use Algorithm::Diff -- if it was more widely available

use strict;
use DBI();

my $debug = 0;

# Connect to sites database
my $dbh = DBI->connect("DBI:mysql:database=floyd;host=localhost", "USERNAME", "PASSWORD", {'RaiseError' => …