MySQL Forums
Forum List  »  Newbie

MySQL Table Query Speeds versus MS SQL
Posted by: Steve Adams
Date: August 08, 2014 09:38PM

First, I should say that I am a Windows and Storage guy, not a DBA

We are doing an evaluation of MySQL Enterprise on a Windows 2008r2 machine with 80 cores and 512gb of ram. 8GB FC to SAN SAS 15k etc. Lots of horsepower...

On a test table with 3 columns and 34+ million rows (not big for us):
This query isn't of particular importance, but an example of the relatively bad performance on full table queries compared to MS SQL.

select COUNT(UNQ_CHECK_NBR) as Total_Baskets
from cid_steve D ;

Explain Query:
'1', 'SIMPLE', 'D', 'index', NULL, 'idx_UNQ_CHECK_NBR', '111', NULL, '33310052', 'Using index'

Takes about 20 seconds to run as innodb and 8 seconds in myisam. UNQ_CHECK_NBR is indexed and there is no primary key defined. I suppose the speed is not surprising because of the number of rows.

On MSSQL2008r2, the on the same hardware, the same (uncached) query runs in about 500ms and there are no indexes or primary keys. Of course I expect that's not going to fly in MySQL which brings me to this forum.

I've gone through the mysqld setup parameters and used a optimization script to help with the mysql config (as much as a noobie can I suppose).

I'm wondering if MS SQL is just a lot better at this kind of thing? Runs much, much better on Linux? I'm sure there are other areas where MySQL excels but this is a very typical use case for me, sql isn't back ending a large multi user data base or website, we have a handful of DBA's running queries and transforming large data sets that will be moved into a reporting application when processing is complete. Could this be an issue of single cpu/core queries?

Options: ReplyQuote


Subject
Written By
Posted
MySQL Table Query Speeds versus MS SQL
August 08, 2014 09:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.