I recently filed Bug #37359 filesort can be more efficient based on some performance work with an industry standard benchmark. Read on if the internals of how MySQL implements filesort interests you.

Filesort, as the name implies, is used to sort records when there is an ORDER BY clause in the query. The reason it has the prefix "file" is because temporary files may be used to store intermediate results. filesort() uses a per thread sort buffer of size sort_buffer_size to sort the table. filesort() is implemented in sql/filesort.cc.

filesort may not know how many records need to be sorted. It asks the storage engine for an estimate of the number of records in the table via estimate_rows_upper_bound(). If the number of records that fit in the sort buffer is less than the estimate, filesort will use temporary files to store intermediate results. The flow …

I recently filed Bug #37359 filesort can be more efficient based on some performance work with an industry standard benchmark. Read on if the internals of how MySQL implements filesort interests you.

Filesort, as the name implies, is used to sort records when there is an ORDER BY clause in the query. The reason it has the prefix "file" is because temporary files may be used to store intermediate results. filesort() uses a per thread sort buffer of size sort_buffer_size to sort the table. filesort() is implemented in sql/filesort.cc.

filesort may not know how many records need to be sorted. It asks the storage engine for an estimate of the number of records in the table via estimate_rows_upper_bound(). If the number of records that fit in the sort buffer is less than the estimate, filesort will use temporary files to store intermediate results. The flow …

Server-side Prepared Statements are an outdated technology from ancient times, are they? Brian gives a long list of arguments in his blog posting “Prepared Statements, Musings” why one should think twice before using server-side prepared statements. PDO does enforce the use of prepared statements for all statements which return a result set. Good or bad?

• What are (server-side) Prepared Statements?
• Pro’s and Con’s
• MySQL myths
• MySQL specifics
• …

If you for some reason missed the news yesterday: Symbian, the largest mobile operating system, will soon be Open Source. The software will be made available “over the next two years” and is intended to be released under Eclipse Public License (EPL) 1.0.

Looking at the Symbian press release:

Mobile leaders to unify the Symbian software platform and set the future of mobile free

Foundation to be established to provide royalty-free open platform and accelerate innovation

LONDON, UK; June 24, 2008 - Nokia, Sony Ericsson, Motorola and NTT DOCOMO announced today their intent to unite Symbian OS?, S60, UIQ and MOAP(S) to create one open mobile software platform. Together with AT&T, LG Electronics, Samsung Electronics, STMicroelectronics, Texas …

So I took Roland's comment and tried to get it integrated into my code. For the tab management it worked well, but for portlet management it was a lot harder. Actually I only have a partial implementation finished. What's missing is the necessary logic to be able to move a portlet in the same tab from one column to another (there are 3 columns a portlet can be in for each tab). The thing that was most important to me was cleaning up the pruning operation. This took some trickery (aka hackery). I think its a better implementation but it does make me a bit nervous. Of course its all still very MySQL only.

Anyways so here goes the final query for moving a tab:

UPDATE user_tabs ut
    JOIN user_tabs ut2
        ON (ut.user_id = ut2.user_id
            AND ut2.id = :id
            AND ut2.pos != :pos
            AND ut.pos >= LEAST(ut2.pos, :pos)
            AND ut.pos …

From 7:30 - 8:30 pm tonight, Wed. June 25th, in the Berkeley room of the Sheraton Boston, I will be hosting a Birds of a Feather conversation entitled “Pros and Cons of Managed Services”. This will go beyond MySQL and even beyond remote database management, and just deal with the overall pros and cons.

Come, share your good and bad experiences, and discuss why managed services may or may not be appropriate for your situation. I will try to take notes at the BoF.

(Note: I have no idea if they check badges for Birds of a Feather sessions or not)

This post is a follow-up to my talk at JavaDeus 2008 where I showed how you can make use of Lucene inside of MySQL using an experimental branch and some triggers and stored procedures.

Since the process is not entirely obvious and the branch is very experimental, I thought it would be a nice thing to put it online. It’s pretty amazing with how little code (in Java and otherwise) you can implement a working full-text search engine that simply doesn’t get into your way :)

Note: Be advised that the MySQL server we will build is likely to crash at times. Do not use it in production! And of course, always have a safe backup of important data.

This first part will cover the basics on how the get the necessary foundation in place, configuring the server and …

I was working on a problem today and I asked myself a question that I keep find myself coming back to.

For the web, we will keep this in context, do we really need to concern ourselves about supporting anything other then UTF8?

For example here is a partial list of character sets MySQL supports:
http://dev.mysql.com/doc/refman/5.0/en/charset-mysql.html

What do I find when I visit web shops?

The ones who either do not know anything about character sets, or do not care, just keep with whatever the default MySQL shipped with.

Otherwise? It is UTF8.

Unless of course they do not know, but it turned out it did matter. Those folks are typically sweating out a future where they know they will be altering all of their tables.

The question remains though, …

Baron Schwartz wrote a neat article on MySQL’s pager command yesterday. As a followup, I thought I would post what I use as my default PAGER environmental variable. It simply substitutes mk-visual-explain for MySQL’s EXPLAIN command and uses less if the output is taller than your current terminal window (note, this currently [...]

This post is a follow-up to my talk at JavaDeus 2008 where I showed how you can make use of Lucene inside of MySQL using an experimental branch and some triggers and stored procedures.

Since the process is not entirely obvious and the branch is very experimental, I thought it would be a nice thing to put it online. It’s pretty amazing with how little code (in Java and otherwise) you can implement a working full-text search engine that simply doesn’t get into your way :)

Note: Be advised that the MySQL server we will build is likely to crash at times. Do not use it in production! And of course, always have a safe backup of important data.

This first part will cover the basics on how the get the necessary foundation in place, configuring the server and …