I got an email today from O'Reilly today about the MySQL Conference (coming up at the end in April), it looks like a pretty good conference, but one of the sessions caught my eye. It was called A Storage Engine for Amazon S3.

Amazon's S3 is a web apt-oriented storage service, making storage available to both ordinary HTTP browsers and to sophisticated applications via SOAP and REST interfaces. Learn how useful it is to make this storage accessable to MySQL users via a plug-in storage engine.

I did some quick googling but I couldn't find the plugin, so I am guessing that it will be released at the conference. Anyone have a link?

This gets interesting when combine this with Amazon EC2, because you are not charged for bandwidth from within amazon's network, and you are connected to S3 on a fast network.

With my very heavy travel load and skilling load I’ve not had time to scratch myself. It hasn’t stopped the brain working overtime on various issues including the classic find a pattern in a string starting with a wildcard character. On a recent gig I saw the true classic.


SELECT columns
FROM users
WHERE username LIKE '%str%'
OR firstname LIKE '%str%'
OR lastname LIKE '%str%'

I went through the various options and comments on leading ‘%’, OR’s, combined columns, FULLTEXT (which doesn’t work in this case), merge indexing etc, however it perplexed me that nobody has really solved this problem, or at least shared their solutions.

I have an idea, a theory and while I’d love to prove/disprove it, I simply just don’t have the time. So here are my notes, hopefully somebody can comment positively/negatively, do some research, or encourage me to pursue it more. …

** This is an editied “re-post” of an item i wrote about a year ago. **

MySQL V5.0 has been out for a while; the current production community version is 5.0.27.

With version 5, MySQL has addressed most of the major objections people have that MySQL is not a “real” database. With support for triggers, stored procedures, views and XA transactions, I think MySQL has a feature set that will enable people to easily migrate to MySQL. At this point in time, InnoDB is the only storage engine choice for applications that require transactions, and this is still a reasonable choice in most cases.

The only open question for MySQL A.B. is resolution of the problems created by Oracle’s acquisition of Innobase Oy, the developers of the InnoDB storage engine. I previously wrote a post about the Oracle Innobase issues, the summary is that Oracle’s acquisition of Innobase Oy is a problem not because of the storage engine, but …

The amount of data out there via API's is increadible these days. For instance you can take an address, and get the latitude and longitude using Google's GeoCoding API.

I am using this API along with some others to build a pretty some interesting stuff (more on that when its public).

Today I needed to calculate the distance between two points, I found a bunch of formulas here to convert two lats and longs into miles. They had some more complicated formulas, but I went with an easier one because approximate accuracy was sufficent. Here's how the formula translated into SQL (tested on MySQL):

SELECT id, place_name,
ROUND( SQRT( POW((69.1 * (#Val(arguments.latitude)# - latitude)), 2) + POW((53 * (#Val(arguments.longitude)# - …

Sometimes you need to think backwards.

Here was the problem. I needed to match up some IP address ranges to the company that owns them. Looking for a simple solution to the problem I came up with storing the IP address block patterns in the database as follows:

ip_pattern
----------------
127.%.%.%
192.168.%.%
10.%.%.%

Any idea why I choose % as the wildcard?

That's right - it's the wildcard operator in SQL for the LIKE statement.

So now when I have have an IP address 192.168.1.1, I can do what I like to call a backwards LIKE query:

SELECT company, ip_pattern
FROM company_blocks
WHERE '192.168.1.1' LIKE ip_pattern

This works on SQL Server and MySQL, and I would think it should work fine on any database server.

Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.

I should point out that using the hints incorrectly will most likley cause your queries to perform worse, so be sure that it actually makes sense to use them before you go nuts. This means use EXPLAIN and read the documentation on each hint before using.

It's also a good idea to enclose the hints within a SQL comment, for example SELECT /*! SQL_NO_CACHE */ columns FROM table. This can help to make your application a bit more portable.

Let's take a look at some MySQL Optimization Hints:

SQL_NO_CACHE

The SQL_NO_CACHE hint turns off MySQL's …

Just came across interesting and really disturbing information about MySql :

As you might noticed there are no recent MySQL Community versions available for download from MySQL Download Area This applies both to binaries (which is expected with new polices) but also to the source files which were promised to be available.

So what is if you would like to use recent MySQL code while staying with community version ? I chatted with Monty on this topic today.

So, if you need mysql binaries for new versions you have 2 options:

1. Use your distribution vendor’s builds (in Gentoo, Ubuntu and Debian)
2. or Build your own binaries from …

I started this post a month ago, but after Kaj’s discussion on the same topic at the MySQL Camp I figured it was time to post.

I had dinner with a friend recently (a very smart friend), and our conversation lead him to ask “What’s different with MySQL?”. One of the things I tried to describe was the “Pluggable Storage Engine Architecture” (PSE) potential for the future that I expect will set MySQL apart from all other Open Source and even commercial databases.

Here are some details of the example I tried to provide, given somebody who understands enough of the general principles of RDBMS’s.

Consider the ability that information (intelligent data) is available within a Relational Database via the appropriate tools and language (e.g. SQL) but it is not physically constrained to Tables, Columns …

It seems, people are clammering for a more consolidated help guide for SQL Performance tips.

Jay Pipes at the MySQL Camp ran a session Interactive Top 10 SQL performance Tips. There was plenty of input and discussion, and at the time Sheeri simply typed them into a wiki page for later work.

Well it seems even that rough list is popular at Del.icio.us ranking near the top of the Hot List on the front page. I saw it earlier and it was second or third, but didn’t think of taking a screen shot until now, but it’s still high.

I’d say that we could easily …

The great thing about this unconference, is the lack of total formal structure. For now , our first session we are having an open introduction of people, there are at good 60+ people here already, and people rolling in, and it’s great to hear people’s background, and also to bag Jeremy Cole at every opportunity. We have a variety of people from various backgrounds, companies and experience levels.

We are in the Kiev room, with power build into the desks, lots of desk space and full 360% swivel chairs. This is just another example of the company’s clear thinking about it’s requirements.

There have already been some very funny stories, I should have made more earlier notes. Here are some.

Adam Ritter (Proven Scaling ride winner) was the first to bag Oracle, really bold move with Ken Jacobs from Oracle …