One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even if function is very simple such as arithmetic operation. Same can apply to order by, if you would like that to use the index for sorting. There are however some interesting exception.

Compare those two queries for example. If you look only at ORDER BY clause you would see first query which sorts by function is able to avoid order by while second which uses direct column value needs to do the filesort:

PLAIN TEXT SQL:

1. mysql> EXPLAIN SELECT * FROM tst WHERE i=5 AND date(d)=date(now()) ORDER BY date(d) \G
2. *************************** 1. row ***************************
3.   …

It might seem odd for Alfresco to be announcing that it has open sourced its JLAN technology - given that the company already releases 100% of its code under the GPL (with a FLOSS Exception) - but today the company announced that it has open sourced its JLAN technology as open source.

Why? Because while JLAN was available before as part of its open source content management solutions, it had also been available under a proprietary license (due to legacy reasons - Alfresco acquired the code from another party back in 2005). Today, JLAN is completely open and offered as a separate component.

Who cares? Well, anyone that wants to offer an embedded virtual file system that offers the only Java client and server implementation of Microsoft Window's CIFS (Common Internet File System) protocol, allowing content, system administration information, and …

Yesterday I've attended MySQL Customers Conference in London. This event is much smaller size than Users Conference (one day and about 170 people attending) and surely less geeky - there were no one from MySQL Development Support or Consulting teams and Sales Engineers were as close as you could get. Though Anders Karlsson and Ivan Zoratti are one of the best of the kind being more technical than quite a few of the teams mentioned.

It started with Robins presentation about MySQL features and Roadmap. Not what it was something new but it was good to hear about release plans for MySQL 5.1 (Q1 2008) and MySQL 6.0 (Q4 2008) - which sounds pretty ambitious to me but we shall see how many resources 5.1 will take after it is released to get all the issues resolved. Falcon will be default transactional storage engine in MySQL 6.0 - I did not truly understand if it will be recommended transactional storage engine or just default storage engine will …

Apple announced, as expected, a release date for Mac OS 10.5 Leopard. The date is October 26th, 2007 in case you missed it. Since everyone on the internet is a guru, and therefore, so am I, I will give you plebes reasons to upgrade to Leopard. But, first let me qualify myself worthy of such preachery (gurus can make up words).

1. I’ve been using Leopard since January 2007, since I’m an ADC Member, and I have early access.
2. I’ve been using it on my primary work machine since September 2007.
3. This is the internet, and somehow you found this post to read, therefore you don’t know any better..

I’m going to assume that the build shipped to us ADC members, has been updated and will not be the build that will be released to the masses, there are still bugs in my release, but it’s leaps and bounds ahead of the August release, and very close to stable.

…

The other day I had a case with an awful performance of a rather simple join. It was a join on tb1.vid = CONCAT('prefix-', tb2.id) with tb1.vid - indexed varchar(100) and tb2.id - int(11) column. No matter what I did - forced it to use key, forced a different join order, it did not want to use tb1.vid index for it. And no surprise it was way too slow, the number of rows analyzed was really huge:

PLAIN TEXT SQL:

1. mysql> EXPLAIN
2.     -> SELECT
3.     ->  tb1.*
4.     -> FROM tb2
5.     -> STRAIGHT_JOIN tb1
6.     -> WHERE
7.     -> (
8.     ->    tb1.vid LIKE 'prefix-%' AND
9.     ->    tb1.vid = CONCAT('prefix-', tb2.ID) AND
…

A good two weeks ago, MySQL 5.1.22 was released. MySQL 5.1 is a new release of MySQL Server, with new features like

• partitioning (likely the top feature in 5.1)
• events (”crontab” triggers in the database)
• row-based replication
• table logs
• some XML functions

and also with major bug fixes, such as the AUTO_INCREMENT table-lock contention in InnoDB (fixed now in 5.1.22), as well as early indications of performance improvements of up to 20 % - 40 % on dual cores in some scenarios.

Jay Pipes has written an overview that compresses all of the pointers to 5.1 into one article, …

Sun recently announced the Sun SPARC Enterprise T5120 and T5220 and Sun Blade T6320 systems based on the UltraSPARC T2 processor.  You can find lots of information on the various features and functionality provided by these servers.

One other cool feature is that these systems ship with Cool Stack pre-loaded. The Cool Stack 1.1 packages are available in /var/spool/pkg. You can install the ones you want using the pkgadd(1M) command as follows :

root@wgs40-82 # cd /var/spool/pkg
root@wgs40-82 # ls
CSKampSrc_sparc.pkg        CSKphplibs_sparc.pkg
CSKamp_sparc.pkg           …

Just noticed in my inbox this morning a piece of email with the
following URL:
http://developer.amazonwebservices.com/connect/ann.jspa?annID=241

So it looks like the days of having to "know someone" to get an EC2
account is now over. It is also nice to see that 64bit instances are
now available.

Last week when I was at the PHP Zend conference talking about how to
deploy MySQL in EC2 and I got several questions that boiled down to
"but can I get a bigger instance"?

Which my answer was "I'm sure that is what they are thinking of
doing", when the real answer could have been "look closely at my
running configuration and you will notice what my instance is".

It is good to see Amazon make the announcement, especially after last
…

In this post I will try to explain how memory management works inside MySQL Cluster.
Memory management differs between different versions of MySQL Cluster.

The general idea is as follows:

• Data (and ordered indexes) is stored in the DataMemory section. The DataMemory is organized in pages where each page is 32KB.
• You can view the DataMemory as a pool of pages consisting of X number of 32 KB pages.
  
• A table is associated with one or more pages from the DataMemory (pool of pages) depending on how much data is stored in the table.
  

That is the general stuff, now let's see how this is used.

5.0 and 5.1

Allocation

1. If the table is empty and there is an insert - one page will be allocated from the page pool (DataMemory) and associated with that table.
   
2. Subsequent inserts will fill up the data …