Yep, here's another weekend challenge. It's not a regular thing, I just post one when I think of something and there's no prizes except eternal glory!

This time, try and come up with a nice clean and efficient way of maintaining ordered lists. That is, all items in a table, or groups of items, should be ordered *inside* that table/group relative to eachother, and it should be possible to move them around (i.e., change that order and insert new or existing items at any point) without too much hassle. That last requirement is probably the main thing you will want to think about...

Naturally, you shouldn't muck around with the auto-inc ID, you know how I feel about that ;-)

Good luck, and have a nice weekend!

I am ready to leave for the FrOSCon Conference, taking place on Saturday and Sunday in St. Augustin/Germany.

Here's the program that contains many MySQL related sessions.

I hope to meet many MySQL people there ;-).

MySQL doesn’t allow referring to a table that’s targeted for update in a FROM clause, which can be frustrating. There’s a better way than creating endless temporary tables, though. This article explains how to update a table while selecting from it in a subquery. The problem Suppose I want to update a table with data from a subquery that refers to the same table. I might want to do this for a variety of reasons, such as trying to populate a table with its own aggregate data (this would require assignment from a grouped subquery), updating one row from another row’s data without using non-standard syntax, and so on.

Code Challenge 2006, sponsored by webdevity.de and O'Reilly in Germany, is open until August 15, and offers some serious prizes - sponsored by MySQL AB and other vendors and magazines.

The code challenge asks contestants to program one or more out of three tasks:

• Webcalendar
  
• Wikipodcast
  
• CAPTCHA (Completely Automated Public Turing Test to Tell Computers and Humans Apart).

See Code Challenge 2006 for all the details!

The vmware config used for this example. This is a quick tour of LVM and a demonstration how it is superior to static partitions. Basically, LVM provides you with a way to create dynamic partitions - you will be able to grow and shrink partitions on demand, move them between disks and snapshot them for backup, all while the filesystem and database on top of it are active and busy.

The LVM tour in this blog post has been created on a vmware instance with a Suse 10.0 Professional installation which I am using to show a combination of RAID and LVM configuration examples. The vmware has a bit of memory, a network card, a boot disk with a text only Suse 10 installation and 8 small simulated SCSI disks besides the boot disk to demonstrate stuff.

Here is the configuration for the basic system.


Continue …

Today I helped one of our lead Cluster developers, Martin Skold, finish up the editing on a article you should see posted on the MySQL Developers Zone page next week. The article is titled, "MySQL Cluster 5.0 ALTER TABLE In-Depth".

The article covers the MySQL ALTER TABLE syntax with code examples, how it works behind the scenes, and using the ndb_show_tables utility to verify results. He also gives us a preview into some of the improvements made to the ALTER TABLE functionality in 5.1.

ALTER TABLE enthusiasts should definitely check this article out. I'll add a link to the article on this blog post once the Web guys make it available.

- Jimmy

Please feel free to forward to interested parties.

Who: Jim Starkey at the Boston MySQL Meetup Group
What: Falcon, the new MySQL storage engine
When:
Monday, July 10, 2006 at 7:00 PM
Where:
MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge, MA 02117
Steps from the Red line, plenty of free parking.

The July Boston MySQL Meetup’s topic is Falcon, the new storage engine for MySQL. Creator Jim Starkey will speak. Jim Starkey has been writing database software for 20 years. He created BLOBs, multi-versioning concurrency for relational databases, cascading update triggers, event alerters, and more. Read more about him at http://tinyurl.com/lno4p and http://tinyurl.com/mym7d.

We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking — you can park in ANY MIT lot after 3 pm, even if it says …

YAMQ (yet another MySQL question) came up at work this past week. Some of our old data warehousing libraries work under the assumption that MySQL can only handle 16 indexes (built during the early 3.23.x days). So the question is how many indexes can a single table have these days?

This is similar to last week's questions on how many joins MySQL can handle. I don't see much in the way of official documentation, but dug around the forums and found good information in this thread.

Creating an index requires creating a key, and there's a limit placed on the number of keys allowed for a table. Thus the limit on indexes is governed by the number of keys you are allowed to create. The error message when you've created one too many keys looks like: