Three small per session tips in improving the speed of loading into Innodb Tables (assuming you already have a correctly configured Innodb environment)

SET FOREIGN_KEY_CHECKS=0;
SET AUTOCOMMIT  = 0;
SET UNIQUE_KEYS = 0; # if you know your data is clean.

I wanted to follow up my earlier post My Ideas for MySQL Camp III with some of motivations. Luckily, good friend and colleague Jeremy literally took all of 15 mins to respond to my post with “So, I see your vision for the event itself. What?s your vision for the results of the event?” (Unfortunately with 150+ draft posts, it’s taken some time to complete my reply).

The present differentiation of MySQL Community and MySQL Enterprise in my eyes is a joke. Now before I start or re-start more then already one flame war let me first talk about MySQL Enterprise. MySQL Enterprise is a great and necessary product offering by MySQL Inc. It serves essential services to essential customers with features such as commercial 24×7 support, MySQL Enterprise Monitor which is an excellent start when clients have nothing (and boy …

The answers to the last pop quiz are up: http://www.pythian.com/blogs/868/pop-quiz-mysql-cluster

So here’s another pop quiz. Given the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16450949 to server version: 4.1.14-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*),length(password) from mysql.user group by length(password);
+----------+------------------+
| count(*) | length(password) |
+----------+------------------+
|       49 |               16 |
|       31 |               41 |
+----------+------------------+
2 rows in set (0.00 sec)

mysql> select password('foo');
+-------------------------------------------+
| password('foo')                           |
+-------------------------------------------+
| *F3A2A51A9B0F2BE2468926B4132313728C250DBF | …

I've just finished reading two recent blog posts about new query optimizations in the upcoming MySQL 6.0, it's all fine and dandy but ...Looking at Correlated semi-join subqueries and PostgreSQL by S. Petrunia we can read something like Quote: The first thing we did was to take a look at PostgreSQL as it is easily available and seems to have at least decent subquery handling (or even better

Some caveats for students willing to try their hand at any Summer Of Code project:

• Please state your programming skills clearly. Don't inflate your previous …

Today, 24 March 2008, the student application period opens. And it remains open for a week, until 31 March 2008, presumably at one minute before midnight UTC.

If you haven’t already discussed the MySQL related ideas published on forge.mysql.com/wiki/SummerOfCode2008Ideas, do so now.

A good place to first silently lurk in and then actively participate in is lists.mysql.com/soc.

As opposed to last year, you can now also apply for MySQL Worklog Items Open for Community Development listed on forge.mysql.com/wiki/ComContribution_Worklog. Worklog items are internal “todo items” or coding tasks identified (but not yet necessarily being …

If you are part of a big company and would like to recommend to them a list of open-source and cheap commercial alternatives, I have compiled a list that I use for my company.

These options might make your company more competitive in certain markets and might let you react faster to changes.


Here is the list:

Database - DataWarehouse
InfoBright - Commercial DataWarehouse Engine (highly recommended) that runs inside MySQL. review about it

EnterpriseDB - Commercial Enterprise Scale Database that runs inside PostgreSQL

Vertica - A very high-scale, …

MySQL has a reputation of being a lightweight database. This definition can be intended either as having limited features or as requiring little hardware.

In my experience, I have seen MySQL used in quite heavy load situations, and thus I will discard the first meaning of lightweight. As for expensive hardware, my own experience is mixed. Yahoo! and Google have shown the world that using an array of inexpensive boxes in replication is often the most sensible way of scaling. However, there are many users who don't feel comfortable with replication, and therefore buy more iron when their current box has reached its limits. Sometimes, an upgrade could be avoided by proper configuration, but this is beyond the point. Given the choice, what is your favorite hardware for MySQL?
There is a …

The work on subquery optimizations goes on, and we're already curious how the assortment of new 6.0 subquery optimizations compares to what is found in other DBMSes. MySQL's new strategies are not DBMS theory breakthroughs, similar ideas have been implemented in other DBMSes, but when you take this list of strategies and put them into this kind of optimizer, the result has no direct equivalents in any other DBMS (or at least we believe so).

The first thing we did was to take a look at PostgreSQL as it is easily available and seems to have at least decent subquery handling (or even better than decent, I have not heard much complaints). And the first interesting difference was handling of correlated subqueries. With exception of materialization, MySQL's new subquery strategies do not care if the subquery is correlated or not.

For example, …

While working with a client we found a query that can crash MySQL. It has to do with ref_or_null outer joins where the table contains a null value. MySQL fails to tell the storage engine to initialize the index before reading from it. It crashes when used with MyISAM tables but it could result in an incorrect key file for table error with other storage engines. You can find the test case and patch in bug #34945. I’ve waited to blog about it until MySQL approved my patch. As of this writing my patch has been approved and committed for release in 5.0.60.