Answering questions asked on the site.

James asks:

Your series on hierarchical queries in MySQL is tremendous! I’m using it to create a series of threaded conversations.

I’m wondering if there is a way to paginate these results.

Specifically, let’s say I want to limit the conversations to return 10 root nodes (parent=0) and all of their children in a query.

I can’t just limit the final query, because that will clip off children. I’ve tried to add LIMITs to your stored functions, but I’m not getting the magic just right.

How would you go about doing this?

A quick reminder: MySQL does not support recursion (either CONNECT BY style or recursive CTE style), so using an …

I (Peter Gulutzan) gave a Performance Schema talk at the O’Reilly MySQL User Conference are available at oreilly.com. My colleague Jimmy Yang gave a talk about InnoDB monitoring which was about 40% devoted to Performance Schema; his slides too are available at oreilly.com.
Edward Screven’s keynote talk also mentioned Performance Schema. I had a few minutes to show it during the “Demo of all the Big New Features” talk which I co-presented with Konstantin Osipov.

The rest of this blog is the text of the talk that I planned for the slides. Naturally it came out differently due to time constraints and questions.

User Conference Slides: …

Typography for Lawyers is a cool use of WordPress for a mini-book. Hat tip: Scots Law Student.

A friend just emailed some suggestions what to do while stuck in SF:

20+ campfires on Ocean Beach
-------------------------------------------------
A fun thing to do any Friday or Saturday night after about 8pm until
after midnight is go to Ocean Beach where hundreds of people make 20+
campfires (folks are friendly -- just walk up and introduce yourself
and join some group's campfire or walk between many -- bring some beer
or snacks to share if you want to).  This beach is at the intersection
of Great Highway and Fulton street, San Francisco

read more

It's not notes about the Conference itself, but about Performance :-) There were many topics discussed and presented around InnoDB performance during UC2010, but time was limited to start long talks as another session interesting as well should start within 5 min :-))

So I've prepared a short summary with some points I consider important on configuring InnoDB to run it optimally, but if I missed something, or you don't agree with me - please comment! :-))

Storage layout - I strongly advice you to separate your data and redo logs, and place them on the different storages/arrays/disks.. - Why?.. - just because of the different I/O nature: redo log activity will mainly generate sequential writes, while data reads/writes are mainly random (specially indexes) - so if you mix these I/O activities together you'll have worse performance for sure (even if you use SSD).. As well if you have binlog activated - don't forgot to place it …

10.  a swarm of geeks storming a hotel bar is awe inspiring
09.  openly shared information is a beautiful thing
08.  i got to be star struck when meeting and greeting luminaries
07.  only to find out they know about my comany/projects/name
06.  met clients/peers/vendors whom i only knew via email/im/twitter
05.  i could describe what i do and get articulate questions in return
04.  i learned new ways to contribute to the community
03.  i met new people and made new connections
02.  i shared opportunities/challenges i face and got new perspectives and ideas
01.  i talked to smart people excited about shared interests and was inspired

Related posts:

1. Ignite MySQL at the MySQL Conference and Expo …

If you are a MySQL conference attendee who cannot get back home to Europe, you can find other fellow MySQLrs at this page on MySQL forge. Please use it as a central hub to organize gettogethers like dinners. Or why not take the opportunity for a hackathon?

As for myself, my BA flight is canceled and there is no way to even rebook (160 minute waiting line to call BA). I will have my travel agent rebook the flight on Monday European business hours. Until then, I'm stuck indefinitely in California.

Peter and Arjen will be at DrupalCon SF 2010. Peter specifically for the event, Arjen staying around the SF area after the MySQL Conference last week.

Specifically, we’ll be talking with people about using the OQGRAPH engine to help with social graphs and other similar problems, easily inside Drupal. You may recall that Peter already created the friendlist_graph extension for the friendlist Drupal module.

From the MySQL Conf and other earlier feedback, OQGRAPH is proving to be a real enabler. And since it’s free/GPLv2 and integrated in MariaDB 5.2, there’s generally no hindrance in starting to use it.

In this note I'll show how to use the color palettes in "slide:ology" by Nancy Duarte (those of us who spoke at the MySQl Conference received it as a nice gift from O'Reilly).
Step 1 is to download Entrance Community edition from our web site: http://dbentrance.com/. You need JRE 1.6 or better and access to a MySQL server to run it. Simply unzip the download and start the program with "java -jar entrance.jar".
To make a chart, click "New" and enter a PLOT command:

   plot

       axislabels, red bar, blue bar, green bar

   select "label", 1, 2, 3;

The SELECT part of the command can be almost any MySQL SELECT statement. (e.g. unfortunately you can't change delimiters yet).
Click "Run" and you have the chart. Now to replace those eye-scorching colors with something better from the book. Take a look at the section "Using the Power of …

I am back home from a good week at the 2010 O'Reilly MySQL Conference & Expo. I had a great time and got to see some old friends I had not seen in a while.

Oracle gave the opening keynote and it went pretty much like I thought it would. Oracle said they will keep MySQL alive. They talked about the new 5.5 release. It was pretty much the same keynote Sun gave last year. Time will tell what Oracle does with MySQL.

The expo hall was sparse. Really sparse. There were a fraction of the booths compared to the past. I don't know why the vendors did not come. Maybe because they don't want to compete with Oracle/Sun? In the past you would see HP or Intel have a booth at the conference. But, with Oracle/Sun owning MySQL, why even try. Or maybe they are not allowed? I don't know. It was just sad.

I did stop by the Maatkit booth and was embarrassed to tell …