SQL v MYSQL

I thought I’d write a little piece comparing SQL and MYSQL. Such comparison are rarely THAT useful – mine is of a single user, on a single user machine, it missed out more than it tells you – however, you might find it interesting non-the-less.

For some testing I’m doing, I just happen to have a couple of million logs records containing details of an email, the email addresses and their sent status along with delivery dates etc.

These originated on an MS SQL server, indeed there were a lot more than 2 million but I stripped all but the latest 2 million for the purposes of testing.  I was rather concerned about the time SQL was taking to do some searches and decided to start from scratch.. ie no indexes etc then see how much improvement I got from the indexes.

It occurred to me that now’s as good a time as any to do a comparison between the latest SQL (2008) and the latest MYSQL (5).

I figured all I’d have to do was copy my SQL table to an Excel file (Export) out of SQL – doddle… well, NO.

You’d think “sorry, you can’t do that” would be sufficient. Instead I received the most muddled error message from MS SQL… it seems that even today, MS can’t manage to put humane error messages up. I’ll not replicate the lot here but suffice it to say I could not understand a word of it.

Instead I had to resort to the very 20th century tactic of exporting 2 million records via a comma-delimited file !!! I have to say it took SQL less than a minute to generate this and it worked perfectly – but for heaven’s sake MS – Excel is your OWN PRODUCT!!!

Well, that experience was bad enough, importing into MYSQL was even WORSE – it took SEVERAL HOURS to import the 2m records into MYSQL.

Anyway, enough of the babble. 1 machine – 4G ram, fast hard drives…. no indexes initially – MS SQL 2008 running on Windows 7, MYSQL running in an XAMPP setup on the same machine. Judge for yourself the results.

 

SELECT count(id) FROM psEmailLog where idnumber=’1198999′

SQL 3 seconds

MYSQL 2 seconds

SELECT count(id) FROM psemaillog where opened=”

SQL 1 second

MYSQL 1 second

SELECT count(id) FROM psEmailLog where emailaddress like ‘peter%’

SQL 3 seconds

MYSQL 2 seconds

And now for something slightly more complex – this is where it gets interesting…

SELECT count(id) FROM psemaillog where emailaddress like ‘%peter%’ and emailaddress not like ‘%x%’

SQL 37  seconds

MYSQL 3 seconds

SELECT count(id) FROM psEmailLog where emailaddress like ‘%.co.uk’ and sent> ‘2009/10/01/’

SQL Failed – due to an out of range value, no useful help.

MYSQL 3 seconds

SELECT count(id) FROM psEmailLog where emailaddress like ‘%.co.uk’ or emailaddress like ‘%.com’

SQL 55 seconds

MYSQL  3.4 seconds

SELECT count(id) FROM psemaillog where emailaddress like ‘%aol%’ and returnedstat=-1

SQL 38 seconds

MYSQL 3 seconds

Ok, I know what you’re going to say…. not a fair competition, what happens when we have 100 users on at once (as has happened), what happens when you introduce indexes… well, I thought of that.

Adding an index to MS SSQL on the EMAILADDRESS took 28 seconds

Adding an index to MYSQL on the EMAILADDRESS took a wopping 132 seconds – one down for MYSQL but then how often do you do THAT.

But let’s see what difference they make (after giving time for any background tasks to take place).

SELECT count(id) FROM psEmailLog where emailaddress like ‘%@%’ and emailaddress like ‘%.co.uk’

SQL  68 seconds

MSSQL 13 seconds

Of course this does not tell you how the systems would compare under load or with many simultaneous users… but there you are – that’s my experience so far.

Advertisements

2 thoughts on “SQL v MYSQL

  1. Good set of queries you ran, but it’s hard for someone else to make much meaning of it without you putting at least more details about your setup. What data types and other restrictions did you use? Even for the email column on SQL Server, is the column char? varchar? nchar? nvarchar? text? varchar(max)? (I guess since you’re using “like” you’re using some *char type). They all could impact performance. Also, what collation? And protocol name? Shared Memory? What MySQL engine did you use?

    Obviously I’m being SQL Server biased since I use it more, but details like that would be helpful to follow your test. Now I’m interested to know. 🙂 Personally, I would think MySQL would be faster since it’s more lightweight and does not have all the features SQL Server does.

    Also, just a pet-peeve (before you get slammed by other SQL Server DB’s), it may be better to say “SQL Server” rather than “SQL” because “SQL” refers to the query language. 🙂

    • The setup is a PC (old server) with Windows 7, 2 gig zeon processor, 4 gig of RAM (2.5 usable), datatypes were largely nvarchar and ints. You’re WAY ahead of me on types but I WOULD be interested to hear back from you. I take the lazy approach and use nvarchar in SQL server for any text, I think MYSQL offered me varchar. Do different string types make a SIGNIFICANT difference? The MYSQL engine came with a newish XAMPP setup. According to the blurb “MySQL 5.1.37 + PBXT engine”. Everything else defaults. I use SQL all the time at work, I only really came across it as I’ve a few WordPress sites hosted (this is my only site actually hosted with WordPress themselves) so I figured I’d better learn a little about PHP and MYSQL and that’s what started that off. And yes I agree about the reference to SQL server.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s