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)# - …

Yesterday I spent a few hours going over the internals of MySQL with
the Falcon team.

At some point we hit the "and why does that work like that?".

It was because of copy and paste, or as I think of it in code as
"evolution by the lucky". When code is well documented, encapsulated,
or just plain understandable one developer will copy and paste it to
another section of the code.

Now where does the lucky part come it? My description sound more like
survival of the fittest?

Well now let us insert reality. Sometimes someone copies and pastes
code because they believe it is doing what they want, and they don't
always get it right. Other times there is a flaw in the code that
isn't found for some time. Survival of code occurs because of luck.

In a single code base you can solve this with libraries. Instead of
…

A small bug/feature, which I created a new bug for at http://bugs.mysql.com/25706. Basically, the CURRENT_DATE() function seems to assign a time to dates, and that time is 00:00:00. In fact, all DATE formats are actually DATETIMEs with the date field of 00:00:00 and hidden.

This interferes with queries that use the date as an actual date and expect the date to include everything up until 23:59:59 of that day. The easiest way to reproduce this:

SELECT IF(NOW() BETWEEN '2007-01-17' AND '2007-01-18','yes','no') AS test\G
test: no
1 row in set (0.00 sec)

In fact, the following query always returns “no”, unless it’s exactly midnight:

SELECT IF(NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE(),'yes','no') AS test\G
test: no
1 row in set (0.00 sec)

This does not make logical …

Our new LAMP Virtual Appliance provides turnkey Apache HTTP Server, PHP, Perl, Python, and MySQL in one easy to use 65MB download.

This appliance features optional use of virtual hard disks for MySQL and web content.  Uniquely, this Virtual Appliance shares the web content via CIFS/Windows Networking for extremely easy to use content publication and mnagement.  Many popular web applications can be deployed using this appliance.

As usual, free to download and use …

Next week, backup experts from Zmanda, will present a webinar on how you can use ZRM for MySQL to backup your MySQL server remotely over the internet. If you are a ISP or host your own site, you would want to attend this webinar. For that matter, the infromation will be useful for any MySQL dba. This is a technical seminar where we will go through step by step of actually implementing the solution. We already published a detailed white paper on this topic. You can get to that white paper by registering with Zmanda Network.

Hope to see you at the Webinar.

Contrary to some reports in the community, MySQL will continue providing binaries both for Windows and other operating systems. All our download pages, including those for MySQL 5.0, have binaries today, and will continue to have them.

The source-only releases we introduced with 5.0.33 (and will continue to provide in the future)are just in addition to the binary-and-source releases. The current latest binary-and-source MySQL Community Server release is 5.0.27, and I expect MySQL 5.0.35 Community Server to be released as binary-and-source within a month, both for Windows and our other platforms. …

Jonathan Haddad writes about REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE. Really, Why MySQL has both of these, especially both are non ANSI SQL extensions ?

The story here seems to be the following - REPLACE INTO existed forever, at least since MySQL 3.22 and was a way to do replace faster and what is also important atomically, remember at that time MySQL only had ISAM tables with table locks and no transactions support. Of course you could use LOCK TABLES but it is not efficient.

The reason REPLACE could be efficient for ISAM and MyISAM, especially for fixed length rows is - it could perform row replacement without reading old data first, and of course because you could set it to replace multiple values at the same time just as you have multiple value INSERT.

As a side note: the …

The objective of this exercise is compiling MySQL from a source tree (BitKeeper) on Mac OS X.

I bought a new laptop, a MacBook running Mac OS X Tiger. I was captivated by the user interface, and I was willing to try this new experience, after a long and successful relationship with Linux (I gave up Windows many years ago). Everything went well, until the moment I tried to compile MySQL from source.
Using the default Xcode tools, I was able to compile the source packages provided with the GA release (5.0.x). But with 5.1 it was a different story. I was not even able to complete the compile part. something was breaking quite soon during the process.

After unsuccessfully trying all the tools made by the MySQL Build Department, I finally decided to take things into my own hands, and to create a reliable compiling environment.
I - Isolating the environmentI decided that the sanest course of action was to get the …

There have been a lot of good talks at linux.conf.au so far that I’ve attended (and the one’s that I’ve missed due to scheduling difficulty, I’ve already started watching some videos of). There are lots of good reports about it on Planet Linux Australia even, but one of the most useful talks in my opinion was Jono Bacon’s talk on Herding Cats and Influencing People (watch the video when its uploaded), his thoughts on running a Community.

He talked about McDonalds, and how they package things, that are consumable by all (his point was that projects generally might even need bite size tasks, not get new contributors chucked into the deep end). Which got me thinking, a little more. McDonalds are in the real estate business, and they’re one of the major players in the fast food industry. I’ve always believed that there are too many Linux distributors in the market, …

REPLACE INTO will actually perform a delete and then an insert, while INSERT … ON DUPLCIATE KEY UPDATE will perform an update (as the name suggests). I would think the latter would be faster. I have not done any performance testing between the two, but it only seems logical the update would be faster than the delete/insert. Please correct me if I’m wrong.

Since the two statements both end up with the same result, I’m not sure yet what the benefit of REPLACE into it.

Yes, I realize both are MySQL additions, so please don’t bother telling me they’re not ANSI standard.

References:

• INSERT… ON DUPLICATE KEY UPDATE
• REPLACE INTO