The MySQL open source database has become the database-of-choice for the new generation of highly-popular Internet companies pioneering new "Web 2.0" innovation. Leading Web 2.0 sites such as YouTube, Flickr, Habbo Hotel, Linden Labs, CyWorld, Technorati, Facebook, FeedBurner, Feedster, Wikipedia, Digg, LiveJournal, Mixi.jp, SimpleStar, PhotoBucket, 37signals, del.icio.us, Trulia, Neopets, and Zimbra have all selected MySQL to power their explosive growth - due to the database's speed and ability to easily "scale-out" on low-cost hardware.

I wrote an article a while back about how to order updates in MySQL so you don’t violate a unique index. I said I’d write another article on how to swap numbers in a sequence with a unique index. This is that article, but I’m going to make it a little more generic: how to reverse a (possibly ordered) sequence. I’ve been thinking about this for a while, wondering if there’s a way I can do it in-place in one statement (I like to pile challenge upon difficulty).

Few days ago I wrote an article about using UNION to implement loose index scan.

First I should mention double IN also works same way so you do not have to use the union. So changing query to:

PLAIN TEXT SQL:

1. mysql> SELECT sql_no_cache name FROM people WHERE age IN(18,19,20) AND zip IN (12345,12346, 12347);
2. +----------------------------------+
3. | name                             |
4. +----------------------------------+
5. | ed4481336eb9adca222fd404fa15658e |
6. | 888ba838661aff00bbbce114a2a22423 |
7. +----------------------------------+
8. 2 rows IN SET (0.00 sec)
9.  
10. mysql> EXPLAIN SELECT …

Read a NewsForge article today about how the two lead developers of the GPU Gnutella client have amended the GPL to include a provision that bans the software's use by military organizations. Specifically, provision states:

the program and its derivative work will neither be modified or executed to harm any human being nor through inaction permit any human being to be harmed.

Interestingly, Richard Stallman, founder of the Free Software movement, doesn't think distributors have the right to restrict the software user's activities by restricting the software's use in this way, though he said "Nonetheless, I don't think the requirement is entirely vacuous, so we cannot disregard it as legally void." It will be fascinating to see how this plays out, as it has further-ranging …

So, there has been quite a bit of interest so far in MySQL Camp, being held on November 10th through 12th in Silicon Valley. The venue is yet to be decided, but there have been a number of options on the table, including possibly Yahoo!, the University of Palo Alto, and, more recently, some talks with friends over at Google. Also, the proposed session topics are filling in nicely, with a nice mix of technical and non-technical content.

Also, I've had a number of people contact me to inquire what exactly is this whole MySQL camp thing about. So I thought I would share some thoughts about what the event is and is not, for those of you unfamiliar with the concept.

MySQL Camp is an unconference, as opposed to a formal, …

BusinessWeek August 16 issue cover feature is called "Valley Boys" describing the new breed of Web entrepreneurs.  The story features Kevin Rose's www.digg.com as well as other Web 2.0 startups such as FaceBook, HotOrNot, YouTube, SixApart, Del.icio.us (whose name they mis-spell repeatedly), all of whom are running MySQL to scale their business without scaling their costs. 

The story describes how the dynamics have changed with regards to venture capital funding …

When I wrote this post about invalid utf8 characters I needed a way to convert the mysql message into a real identifier for me to take a look at. Below is a quick and dirty script to figure out the bad rows.

I wrote the script below in literally 2 mins. It's really basic and basically just CROSS JOINs two versions of the table one in utf8 the other in latin1 and reports back which string column is not correct. I haven't cleaned the script up, it's ugly and for my purposes will only exist for a short period of time.

test.$TABLE = is the original table latin1 for instance
- use mysqlimport this forces data to not get converted to latin1 from utf8 by setting the character set to binary (i.e. do no convert)

$DB.$TABLE = is the new utf8 table.

#!/usr/bin/php …

Some people might have lives, but I have a webserver.

I think I’ve now upgraded just about everything (software-wise) that’s upgradable on this machine:

• Apache 1.3.33 -> 1.3.37 (Thanks for hiding the win32 binaries under “Archives” when the *nix version is out in plain view, guys)
• PHP 5.0.3 -> 5.1.4 (This required ditching my old php.ini file and doing a new one from scratch)
• MySQL 5.1.8 -> 5.1.11 (Dead easy, even on Windows - yea, TEAM!)
• Perl 5.8.7 -> 5.8.8
• Python 2.3.2 -> 2.4.3
• Tcl 8.4.12 -> 8.5.0
• BlogCMS .3.4.6 -> WordPress 2.0.4 (The RSS feed was broken, I was getting tired of seeing my posts quoted elsewhere sans formatting, and every time I tried messing with the code, it just got worse)
• Singapore 0.9.11 -> 0.10.0 (The one part of BlogCMS that I still really liked after switching …

The Free Standards Group (FSG), a nonprofit organization dedicated to developing and promoting open source software standards, today announced that two of the most important and widely-used applications on Linux: RealPlayer and the MySQL database, are certifying to the Linux Standard Base. The result will be decreased build, porting and support efforts for the software developers and increased choice for end users. For the first time, a single RealPlayer or MySQL package will run on the many LSB-certified Linux distributions including Red Hat, Novell, Ubuntu, Red Flag and many others.

Quite a few people have chimed in on a recent discussion about PHP, MySQL, database abstraction layers, and performance. I think enough viewpoints have been covered that I don't need to comment, but one question I don't see answered is "what are the qualities of a good SQL abstraction layer?" I think it's a very interesting -- and complicated -- question. As it turns out, the term has several meanings, and I think it's important to understand them. In this article I explain one way to categorize different types of database abstraction layers, the goals for each, and some useful success criteria.