Category Archives: Blog Replies

The Future of MySQL (EU Crunch Time)

You’ve probably seen Monty’s post Help Saving MySQL. This is about

  1. Development (will Oracle put significant effort into MySQL, actually innovating)
  2. Brand (“MySQL” has a huge footprint), the trademark owner can enforce this – there have already been issues with companies offering MySQL related services via Google AdWords not being able to use the word MySQL in their ad text even though it was correctly used as an adjective.
  3. Forking is fine, but still has to deal with the branding. For MySQL, that’s possibly the most significant issue of any OSS product ever encountered. You’re not competing against a company, but against an existing brand footprint that you (because of the trademark) have to steer clear of. So “just fork it” is not an easy or short term option, there’s more involved than technical/development work.
  4. Code IP – to some degree (IMHO less important), it’s the thing that enables dual licensing. I regard dual licensing as a pest that’s best got rid of.

The really important thing to realise is that this is not about “killing Sun to save MySQL”, or “sending the right message to investors”. The former is merely a consequence of Oracle’s unwillingness to discuss any other option (whether rightfully or not, that’s just a fact) and the latter has no direct bearing on what’s right for either MySQL or Oracle – it’s definitely a factor that the investor world may consider, but it wouldn’t be a consideration for the EU.

With all that noted… please look at Monty’s post, he provides options and links to for you to action whichever way forward you feel is appropriate, whether for or against or neutral towards Oracle being able to take over Sun with MySQL in unmodified fashion. I think it’s good for more users (essentially interested parties) to express their opinion, since Oracle has managed to mobilise its own customers to flood the EU with their angle. While valid, the result ends up being a tad one-sided!

As I wrote on my comment/update on the Possible Movement in the Oracle/Sun/MySQL/EU Case, it’s unfortunate that the rumour suggesting that Oracle was willing to have MySQL as a separate business entity turned out to not be true, as I reckon it would have been a useful outcome for both Oracle and MySQL. A company can’t/won’t disrupt itself, and there are serious business-related “conflicts” to deal with if a single company sells both both products. Corporate structures and sales will always make decisions to steer away from competing with itself, and generally choose the most profitable road. Which one of the two that is in this case is not relevant, my take is that in the market both Oracle and MySQL have their place, so having either one lose out would not be good.

Irrespective of good intentions, companies do abide by certain rules – well actually many companies are ignorant of them and waste tons of money essentially trying to defy gravity. In any case, for me the issue is not with Oracle having good intentions or mistrusting that, the issue is that not even Oracle can defy gravity. The effort will go where the money is.

Remember what I quoted long ago about IBM and the PC? (Innovator’s Dilemma – Clayton Christensen), IBM planted the new department in another state with its own management and finances, because they knew that in the corporate/management decisions, inevitably the existing mainframe business would win and thus prevent any cannibalisation (from within) of its position. In a nutshell, a company can’t disrupt itself. It’s well documented. I think that overall, the Oracle/Sun deal is a good match. But also, I think MySQL needs to be handled properly to make sure that both MySQL and Oracle (the db product) will thrive in the future. I feel that’s what’s important.

On partial indexes for string columns

After reading Fernando Ipar’s interesting post on partial indexes for string columns, there were two things I wanted to note:

First, this trick works quite well, but only if your like clauses only ever use the wildcard on the right hand side (or not at all). MySQL will not be able to use the index if the like contains a wildcard on the left.

Consider the following table definition:

mysql> show create table people\G
*************************** 1. row ***************************
Table: people
Create Table: CREATE TABLE `people` (
`person_id` int(15) NOT NULL default '0',
`username` varchar(255) default NULL,
`email` varchar(255) default NULL,
PRIMARY KEY (`person_id`),
KEY `people_username` (`username`(5))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Now, see the following queries:


mysql> explain select username from people where username like 'jo%';
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+-------------+
| 1 | SIMPLE | people | range | people_username | people_username | 8 | NULL | 394 | Using where |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql> explain select username from people where username like '%jo';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | people | ALL | NULL | NULL | NULL | NULL | 128928 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)


mysql> explain select username from people where username like '%jo%';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | people | ALL | NULL | NULL | NULL | NULL | 128928 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

As you can see, the first one nicely uses the index. The second and third though, cannot use it at all.
Now, we add an index on the full username field:


mysql> create index people_username_full on people(username);
Query OK, 128928 rows affected (6.72 sec)
Records: 128928 Duplicates: 0 Warnings: 0

Then, rerunning the queries we see the result changing:


mysql> explain select username from people where username like 'jo%';
+----+-------------+--------+-------+--------------------------------------+----------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+--------------------------------------+----------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | people | range | people_username,people_username_full | people_username_full | 258 | NULL | 143 | Using where; Using index |
+----+-------------+--------+-------+--------------------------------------+----------------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)


mysql> explain select username from people where username like '%jo';
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | people | index | NULL | people_username_full | 258 | NULL | 128928 | Using where; Using index |
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)


mysql> explain select username from people where username like '%jo%';
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | people | index | NULL | people_username_full | 258 | NULL | 128928 | Using where; Using index |
+----+-------------+--------+-------+---------------+----------------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

Notice how all queries are now using the index. Even the first query now uses the longer but more detailed index.

The second suggestion I wanted to make is one that is exploiting this index option for right-oriented ‘like’ searches. A common use is for email addresses. Often, they are being searched by e.g. domain name. If you add an extra field to your table that stores the reverse of the email field, you can then use a partial index to index the email field from the right. You will need the MySQL function REVERSE() for this:


mysql> alter table people add email_reverse varchar(255) null;
Query OK, 128928 rows affected (4.63 sec)
Records: 128928 Duplicates: 0 Warnings: 0

mysql> desc people;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| person_id | int(15) | NO | PRI | 0 | |
| username | varchar(255) | YES | MUL | NULL | |
| email | varchar(255) | YES | | NULL | |
| email_reverse | varchar(255) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> update people set email_reverse = REVERSE(email);
Query OK, 128928 rows affected (1.36 sec)
Rows matched: 128928 Changed: 128928 Warnings: 0


mysql> create index people_email_rev on people(email_rev(10));
Query OK, 128928 rows affected (6.72 sec)
Records: 128928 Duplicates: 0 Warnings: 0

Now, you can execute queries with right handed ‘like’ clauses using the partial index:


mysql> explain select username from people where email_reverse like 'moc.oohay%';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| 1 | SIMPLE | people | range | people_email_rev | people_email_rev | 13 | NULL | 1 | Using where |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select username from people where email_reverse like REVERSE('%yahoo.com');
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| 1 | SIMPLE | people | range | people_email_rev | people_email_rev | 13 | NULL | 1 | Using where |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)