Today I found a question in a newsgroup asking for help with creating a join. This join should take a date out of table a and find the record in table b which has a date less than that of table a, but still closest to that date. The tables should be joined through the id column.

Here's my example data (I've changed it a bit from the original request):

mysql> SELECT * FROM a;
+-----+----+------------+
| row | id | d          |
+-----+----+------------+
|   1 | 15 | 2005-12-25 |
|   2 | 20 | 2005-12-29 |
|   3 | 25 | 2006-01-12 |
|   4 | 30 | 2006-01-15 |
+-----+----+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM b;
+-----+----+------------+
| row | id | d          |
+-----+----+------------+
|   1 | 15 | 2005-12-19 |
|   2 | 15 | 2005-12-21 |
|   3 | 15 | 2005-12-27 |
|   4 | 20 | 2005-12-11 |
|   5 | 20 | 2005-12-30 | …

The information_schema tables offer all kinds of useful metadata. Also all information about indexes are included in them, although they are split up among several tables.

I've built up a query that shows all the information about indexes, here it is:

SELECT a.table_schema, a.table_name, a.constraint_name, 
       a.constraint_type,
       convert(group_concat(DISTINCT b.column_name
       ORDER BY b.ordinal_position SEPARATOR ', '), char) 
       as column_list,
       b.referenced_table_name, b.referenced_column_name
FROM information_schema.table_constraints a
INNER JOIN information_schema.key_column_usage b
ON a.constraint_name = b.constraint_name AND
   a.table_schema = b.table_schema AND
   a.table_name = b.table_name
GROUP BY a.table_schema, a.table_name, a.constraint_name, a.constraint_type,
         b.referenced_table_name, b.referenced_column_name
UNION
SELECT …

One of my readers asked for a demonstration of installing an using the new MySQL Workbench Alpha. I thought it would be ideal to make a quick demo of the process with Camtasia and provide it for anyone looking to use MySQL Workbench.

This is more of a quick off the cuff demo, but hopefully it gives you a feel for what is coming in the new Workbench tool.

For those new to Workbench, MySQL Workbench is an entity design tool you can use to design a new MySQL schema.

To watch the video, click here. Macromedia Flash is required. I have also added this video to my video page.

i?ll be giving a talk about the latest mysql features at the southern california linux expo next month (out near lax), and then giving at least one talk (on embedded mysql) at the mysql users conference 2006 in april up in the bay area.

early admission for both conferences is still available. scale4x is $50 until january 16 (or less if you get your hands on a promotional code), the mysql users conference is $945 until march 6 (with various discounts available, like the 15% o?reilly conference alumni discount).

This article is somewhat long. Interestingly, it does not actually cover my entire talk, as there is much to talk about besides the mechanics of each backup option. I wonder what I’d need to do to make this into a white paper or an article?

The backup presentation was finished last night. I may decide to go back and put some extra stuff in there, but that would be syntax and code and stuff. The logic is all in there, and the notes have been printed. I will post the slides (in .pdf and .swf (flash, the file is very small that way) formats) after the talk on Monday, as I may yet revise them.

I am very excited about one slide in particular, and I’ll share it here. It’s really a slide that I end with, but I feel as though it’s a great starting point as well as a summary point. I haven’t seen this information encapsulated this way before, so here goes:

Comparison Table of MySQL Backup Methods …

Yesterday I finished my travel and accomodation arrangements for my trip to the FOSDEM 2006 conference in Brussels, which will take place on February 25th-26th. So if you would like to meet and discuss MySQL-related issues, please get in touch with me! Looking forward to see you there.

I'll be teaching the MySQL 5.0 for DBAs training course in Sydney, 6-10 Feb. There are still a few places available, so if you were looking at MySQL training in Australia, this may be for you!

Note that beginning with MySQL 5.0, we have split our training courses (and certification) into separate components for Developers and DBAs. So the abovementioned course is about topics like management and optimization, and not about basic use and developing apps.

I like teaching training courses, there are always very interesting discussions and ideas. Nothing beats the real world...
Anyway, our training courses are highly interactive, and students have individual machines to do exercises on (or their own laptop, if they prefer). So it's not at all like a lecture (eew, boooooring ;-)

I'll be teaching the MySQL 5.0 for DBAs training course in Sydney, 6-10 Feb. There are still a few places available, so if you were looking at MySQL training in Australia, this may be for you!

Note that beginning with MySQL 5.0, we have split our training courses (and certification) into separate components for Developers and DBAs. So the abovementioned course is about topics like management and optimization, and not about basic use and developing apps.

I like teaching training courses, there are always very interesting discussions and ideas. Nothing beats the real world...
Anyway, our training courses are highly interactive, and students have individual machines to do exercises on (or their own laptop, if they prefer). So it's not at all like a lecture (eew, boooooring ;-)

We often hear about open source vs. commercial software, but what about when there is no commercial software?

Believe it or not, this actually happens. Last month I was at an industry tradeshow, and I walked into a conference session about software. In of itself, this was a rarity--usually, trade show attendees are too busy buying and selling to listen to software vendors. The closet you might get at one of these shows is a talk on search engine optimization or email marketing.

Even more amazing, this session was well-attended. After a while, I realized why. This industry had no commercial software vendors serving it, because it is an industry with a lot of small businesses and very complicated business processes. The former means that specialized commercial applications vendors couldn't serve them profitably. The latter means that off-the-shelf desktop accounting programs couldn't really satisfy their needs.
…

The MySQL Meetup group meeting on Monday, January 9th will focus on backups — I will give a 30-45 minute presentation on backups and we will then delve into a discussion — how folks within the group are backing up, how they’re not backing up, how they’d like to back up. If there’s time, we’ll do a “lessons learned from backup horror stories”.

Afterwards, we’ll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month’s presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month’s presentation but not the discussion)

More details, including the event location and directions, are at: …