Three days ago I’ve finally managed to push the code for WL#3985 “Subquery optimization: smart choice between semi-join and materialization” into MySQL 6.0. I missed the clone-off date so it won’t be in the upcoming MySQL 6.0.9 release, the only way to get it before the next 6.0.10 release is from the lp:mysql-server/6.0 bazaar repository.

What’s new in the push

Before WL#3985, 6.0’s subquery optimization had these three deficiencies:

1. For semi-join (see cheatsheet for definition) subqueries, you had to make a choice between having the optimizer use materialization or all other strategies. The default behavior was not to use …

Looking back at 2008:

This has really been a crazy year. Probably the first year I truly felt I couldn’t keep up with everything that was happening around me (in a good sense). Not because of the depression in the market but rather because many different aspects of our business, community and eco-system have accelerated. Amidst these changes I have also taken on additional roles at Zend to help drive the next phases of our multi-year strategy.

For Zend this has been an important year in delivering on our long term strategy and plan. The PHP Collaboration project which we announced at the end of 2005 has really come to fruition and delivered on its promise including:

- Zend Framework: This year we have had three major releases of Zend Framework, …

After reading at a lot of places for the the single repeatative question, “What engine shall I choose – MyISAM or Innodb?”, this is what I’ve got. Following are points…

The post Choosing between MyISAM and INNODB – MySQL Storage Engines first appeared on Change Is Inevitable.

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about…

The post Using MySQLTuner – MySQL Optimization Tool first appeared on Change Is Inevitable.

The perl script is mainly created to avoid manual Mysql Server Maintenance. The script uses Perl module DBI. You need to provide access credentials and database name(optional). Regarding Analyse, Optimize…

The post Perl Script for Analyze – Optimize – Repair Mysql Databases first appeared on Change Is Inevitable.

Here the division between td1.c1 and td2.c2 is correct:

select td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03    
  from testdata td1,         
       testdata td2   
 where td1.c1 = -99 
   and td2.c2 = 0.03 
 limit 1;

+------+------+---------------+------------+
| c1   | c2   | td1.c1/td2.c2 | -99 / 0.03 |
+------+------+---------------+------------+
|  -99 | 0.03 |    -3300.0000 | -3300.0000 |
+------+------+---------------+------------+
1 row in set (0.00 sec)

Here DISTINCT is added to the query.  The result is incorrect:

select distinct td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03    
  from testdata td1,         
       testdata td2   
 where td1.c1 = -99 
   and td2.c2 = 0.03 
 limit 1;

+------+------+---------------+------------+
| c1   | c2   | td1.c1/td2.c2 | -99 / 0.03 |
+------+------+---------------+------------+
|  -99 | 0.03 |     -999.9999 | -3300.0000 |
+------+------+---------------+------------+
1 row in set (0.00 sec)

…

This post is for people who are trying to roll back unwanted modifications to their MySQL database. You cannot use the binary logs to undo unwanted changes to your data. The binary logs are for redoing statements, not undoing them. If you have a backup, you may be able to restore the backup and then replay binary logs to roll forward to the desired state. But you cannot roll backwards with the binary logs.

MySQLPreacher has only started ten days ago, but despite it has limited viewers so far it would still like to wish all readers a great new year full of joy and happiness, with no deadlocks or failing backups, no overloaded slowquery logs. Hope it will be a year with an enviable uptime!

The gearmand job server written in Perl (current production server from Danga):

mysql> SELECT length(gman_do("reverse", repeat('x',10000000))) AS test;
+----------+
| test     |
+----------+
| 10000000 |
+----------+
1 row in set (49.08 sec)

The new gearmand job server written in C:

mysql> SELECT length(gman_do("reverse", repeat('x',10000000))) AS test;
+----------+
| test     |
+----------+
| 10000000 |
+----------+
1 row in set (0.30 sec)

Mmm, efficiency. Oh, and are those some new MySQL UDFs? Much more coming soon…

Its been a while since I blogged.. and my new year's resolution would be to blog more.

• I could say that I have been very busy at work. I can't really say that I haven't been using MySQL, since I've been using it more these days than I have in my entire career.
• I could say that I am noticing a trend to blog less in the MySQL community. I could be wrong about that, but I definitely don't see the same names blogging as the ones I used to see a year ago. But that could be that some people just moved on.
  
• I could say that I have just been lazy. My blog statistics certainly reflect that.
• Finally, I could say that I lost a bit of enthusiasm for blogging about MySQL. This could be that my priorities have shifted slightly, but I plan to get myself a bit more involved with database developing/administrating in the very near future.

Other things on my list for the …