MySQL and SQL Server 2008 R2 - Simple Query Performance

Question

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

SELECT email from Users WHERE id=1 

Currently, the database has only one table with 3 users. MySQL time is on average 0.0003 and SQL Server is 0.05. Is this normal, or is the MSSQL server configured incorrectly?

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

Answer 1

Are servers of the same power level? Equipment also matters. And are there approximately the same number of people accessing db at the same time? Are there any other applications using the same equipment (databases should not use servers with other applications at all).

Personally, I would not worry about this difference. If you want to see what works better, add millions of records to the database, and then check the queries. The database as a whole all works well with simple queries on tiny tables, even poorly designed or misconfigured. To find out if you have performance problems, you need to test large amounts of data and many concurrent users on equipment similar to what you will have in prod.

Answer 2

The problem with diagnosing low-cost queries is that a fixed cost can put out variable costs. Not that I was MS-Fanboy, but I am more familiar with MS-SQL, so I will talk about this in the first place.

MS-SQL probably has more overhead for query optimization and analysis, which adds a fixed cost to the query when deciding whether to use the index when looking at statistics, etc. MS-SQL also logs a lot of material about the query to plan when it is executed, and stores a lot of data for future optimization, which adds overhead

This will be useful when a query takes a long time, but when analyzing a single query, it seems to have a slower result.

Answer 3

There are several factors that may affect this test, but the most significant is probably the way MySQL caches queries .

When the query starts, MySQL will cache the query text and the result. When the same request is issued again, it will simply return the result from the cache and will not actually execute the request.

Another important factor is the SQL Server metric - this is the total elapsed time, and not just the time it takes to find this record, or pull it out of the cache. In SQL Server, turning on SET STATISTICS TIME ON will change it a bit, but you still can’t compare with the likes.

Lastly, I'm not sure if the goal of this benchmarking is to simplify the request. Are you comparing platforms for a new project? What are your selection criteria?