MySQL vs SQL Server 2008 R2 simple select query performance

Question

Can anyone explain to me why there is a dramatic difference in performance between MySQL and SQL Server for this simple select statement?

SELECT email from Users WHERE id=1

Currently the database has just one table with 3 users. MySQL time is on average 0.0003 while SQL Server is 0.05. Is this normal or the MSSQL server is not configured properly?

EDIT:
Both tables have the same structure, primary key is set to id, MySQL engine type is InnoDB.
I tried the query with WITH(NOLOCK) but the result is the same.

Do the tables have similar structure? Do both have a (PRIMARY KEY) index on id? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 12, 2012 at 6:49
I suspect it might be due to the different way MySQL and MSSQL issue locks. InnoDB uses a technique called Consistent Nonlocking Reads which is supposed to be more lightweight(I have not gone into detail) than actually locking the row like MSSQL will do. COuld you try the query on MSSQL as follows: SELECT email FROM Users WITH(NOLOCK) WHERE id=1 and compare. This is only a hunch though. — Namphibian
– Namphibian, Commented Jun 12, 2012 at 7:23
Might be interesting to try a query that accesses no data at all in both, such as SELECT CURRENT_TIMESTAMP. — Martin Smith
– Martin Smith, Commented Jun 12, 2012 at 8:52

HLGEM · Accepted Answer · 2012-06-15 16:58:43Z

2

Are the servers of the same level of power? Hardware makes a difference, too. And are there roughly the same number of people accessing the db at the same time? Are any other applications using the same hardware (databases in general should not share servers with other applications).

Personally I wouldn't worry about this type of difference. If you want to see which is performing better, then add millions of records to the database and then test queries. Database in general all perform well with simple queries on tiny tables, even badly designed or incorrectly set up ones. To know if you will have a performance problem you need to test with large amounts of data and many simulataneous users on hardware similar to the one you will have in prod.

answered Jun 15, 2012 at 16:58

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

matino Over a year ago

The problem was that MySQL server was running on localhost and MSSQL on another host and the benchmarking time measured included network latency... Since you were the closest with your answer, I accept it but would like to thank other folks for their input :) And yes, I was comparing platforms for a new project and tried first with a basic query...

David Manheim · Accepted Answer · 2012-06-15 15:37:50Z

1

The issue with diagnosing low cost queries is that the fixed cost may swamp the variable costs. Not that I'm a MS-Fanboy, but I'm more familiar with MS-SQL, so I'll address that, primarily.

MS-SQL probably has more overhead for optimization and query parsing, which adds a fixed cost to the query when decising whether to use the index, looking at statistics, etc. MS-SQL also logs a lot of stuff about the query plan when it executes, and stores a lot of data for future optimization that adds overhead

This would all be helpful when the query takes a long time, but when benchmarking a single query, seems to show a slower result.

answered Jun 15, 2012 at 15:37

David Manheim

2,6342 gold badges29 silver badges42 bronze badges

Comments

Code Magician · Accepted Answer · 2012-06-15 17:14:00Z

There are several factors that might affect that benchmark but the most significant is probably the way MySQL caches queries.

When you run a query, MySQL will cache the text of the query and the result. When the same query is issued again it will simply return the result from cache and not actually run the query.

Another important factor is the SQL Server metric is the total elapsed time, not just the time it takes to seek to that record, or pull it from cache. In SQL Server, turning on SET STATISTICS TIME ON will break it down a little bit more but you're still not really comparing like for like.

Finally, I'm not sure what the goal of this benchmarking is since that is an overly simplistic query. Are you comparing the platforms for a new project? What are your criteria for selection?

Collectives™ on Stack Overflow

MySQL vs SQL Server 2008 R2 simple select query performance

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related