MySql Performance issue vs SQL-Server

Question

I've created a table with 50 million rows on MSSQLServer 2008R2 and MYSQL WorkBench 6.1.

I'm not sure why this query take 16Sec on MySql but 0 Sec on SQL-Server?

SELECT  avg(speed) FROM tm where RoadId%5=0 and time%3=0;

-Performance difference exists with different where clauses.

-SQL-Server and MySQL both installed in one machine with 128 GB of Ram and 16 core CPU.

ps: This is script to create table on SQL-Server

CREATE TABLE [dbo].[TM](
    [RoadId] [int] NOT NULL,
    [Date] [smallint] NOT NULL,
    [Time] [tinyint] NOT NULL,
    [VType] [tinyint] NOT NULL,
    [Speed] [tinyint] NOT NULL,
 CONSTRAINT [PK_TM_1] PRIMARY KEY CLUSTERED 
(
    [RoadId] ASC,
    [Date] ASC,
    [Time] ASC,
    [VType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and MySQL:

CREATE TABLE `tm` (
  `RoadId` int(11) NOT NULL,
  `Date` smallint(6) NOT NULL,
  `Time` tinyint(3) unsigned NOT NULL,
  `VType` tinyint(3) unsigned NOT NULL,
  `Speed` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`RoadId`,`Date`,`Time`,`VType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Well those are totally different products and most likely neither of them have been optimized for searching modulo, which quite frankly sounds artificial and not a real case. — James Z
– James Z, Commented Apr 8, 2015 at 16:24
@JamesZ thanks for your reply, this is real table with 500-1000 million of rows. — Ben
– Ben, Commented Apr 8, 2015 at 16:31
If you need modulo a lot, it might be a better idea to calculate into the table, instead of doing it in the search. — James Z
– James Z, Commented Apr 8, 2015 at 16:43
@JamesZ where clause can be anything so I can't calculate them to a table. — Ben
– Ben, Commented Apr 8, 2015 at 16:48
Will you have enough RAM to cache the entire dataset? If not, then this test is bogus because it was cached. — Rick James
– Rick James, Commented Apr 8, 2015 at 20:56

Jon Tirjan · Accepted Answer · 2015-04-08 16:38:51Z

1

SQL Server automatically implements parallelism for this type of query, whereas mysql does not. This basically means that SQL will divide up the table scan and analyze rows with multiple threads. I believe mysql requires the data to be partitioned in order to achieve this same functionality.

answered Apr 8, 2015 at 16:38

Jon Tirjan

3,7242 gold badges18 silver badges28 bronze badges

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

1 Comment

Rick James Over a year ago

Still, 0 seconds sounds fishy. No MySQL will not perform this query in parallel even if PARTITIONed.

Collectives™ on Stack Overflow

MySql Performance issue vs SQL-Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related