MySQL vs. MSSQL on the Amazon Cloud

Recently I’ve been working on a application that requires some serious database performance and had chosen MySQL as the engine based on the common wisdom that MySQL is faster, and cheaper, than MSSQL. After some serious performance problems on a couple of local machines, I decided to use Amazon’s MySQL RDS service and compare it’s performance to their Windows 2008 / MSSQL 2008 instance. This is by no means and exhaustive study, but it is informative at least.

Amazon Instance Type

  • Large Instance 7.5 GB of memory, 4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each), 850 GB of local instance storage, 64-bit platform

Table Structure

  • 5 VarChar(50) columns
  • ~2.1 Million Records
  • Un-Indexed
  • No Primary or Foreign Key Constraints

SQL Process

  • Table data copy using: INSERT INTO myTable2 (SELECT * FROM myTable1)

Processing Time

  • MySQL: Killed at +15 minutes
  • MSSQL: Completed at 1 minute 33 seconds

For large data moving operations, the winner is clear — MSSQL outperforms MySQL by an enormous margin. In further testing I’ve also found simple select operations on MySQL to be lacking as well. Looks like Microsoft had a leg up on the database world finally.

Zip Code Radius Search using MySQL

I had a project that required me to get a list of zip codes within a certain distance from a given zip code.

First, you are going to need to get a database that maps zip codes to longitude & latitude.

Zip code databases:

  1. Free from: http://www.populardata.com/downloads.html
  2. 5$ from http://www.teamredline.com/zc/

Next, you need to create a MySQL function to calculate distances between two longitudes and latitudes:

DROP   FUNCTION  IF EXISTS `GetDistance`

CREATE   FUNCTION  `GetDistance`(
lat1  numeric (9,6),
lon1  numeric (9,6),
lat2  numeric (9,6),
lon2  numeric (9,6)
)  RETURNS   decimal (10,5)
BEGIN
DECLARE  x  decimal (20,10);
DECLARE  pi  decimal (21,20);
SET  pi = 3.14159265358979323846;
SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180  ) + cos(
lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos(  abs ( (lon2 * pi/180) -
(lon1 *pi/180) ) );
SET  x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x );
RETURN  ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;
END

You will now need to create a MySQL stored procedure that accepts a zip code and a radius and returns a list of zip codes that fall within the given radius:

DROP   PROCEDURE  IF EXISTS `GetNearbyZipCodes`

CREATE   PROCEDURE  `GetNearbyZipCodes`(
zipbase  varchar (6),
range  numeric (15)
)
BEGIN
DECLARE  lat1  decimal (5,2);
DECLARE  long1  decimal (5,2);
DECLARE  rangeFactor  decimal (7,6);
SET  rangeFactor = 0.014457;
SELECT  latitude,longitude  into  lat1,long1  FROM  tbl_zipcodes  WHERE  zipcode = zipbase;
SELECT  B.zipcode
FROM  tbl_zipcodes  AS  B
WHERE
B.latitude  BETWEEN  lat1-(range*rangeFactor)  AND  lat1+(range*rangeFactor)
AND  B.longitude  BETWEEN  long1-(range*rangeFactor)  AND  long1+(range*rangeFactor)
AND  GetDistance(lat1,long1,B.latitude,B.longitude)  <= range;
END

Lastly you need to make a call to this stored procedure:

CALL GetNearbyZipCodes( '85254' , 30
Posted in Uncategorized. Tags: . 1 Comment »