2

I've noticed that a table can take 10 times more disk space in MySQL compared to SQL Server. I found this by creating tables in each database with 100 columns and 1000 rows:

  • The MySQL one has 100 nullable columns of type TEXT
  • The SQL Server one has 100 nullable columns of type VarChar(MAX)
  • Each has a primary key of type INT

I then inserted 1000 rows with GUIDS in all columns for both databases. Here are the results I got:

  • MySQL: 499 MB
  • SQL Server: 41MB

Why is this so different between the two databases? I see the same result even when I do this test on new, empty databases.

Here's the MySQL table definition I used:

create table data1(
    id MEDIUMINT NOT NULL AUTO_INCREMENT, 
    column1 text null, 
    column2 text null, 
    column3 text null, 
    -- ...
    column99 text null, 
    column100 text null, 
PRIMARY KEY (id))

And here's the SQL Server one:

create table data1(id int IDENTITY(1,1) PRIMARY KEY, 
    column1 varchar(max) null, 
    column2 varchar(max) null, 
    column3 varchar(max) null, 
    -- ...
    column99 varchar(max) null, 
    column100 varchar(max) null)
Dan Getz
  • 8,774
  • 6
  • 30
  • 64
Luke101
  • 63,072
  • 85
  • 231
  • 359
  • Can you include the exact `CREATE TABLE` statements you used on MySQL and SQL Server? – Tim Biegeleisen Jul 18 '16 at 04:04
  • how about indexes? how about db engines in mysql? (myisam, innodb, mariadb and etc.). also how about my.cnf file (size of innodb log files and etc.) everything depends on how You created tables and what tuning You've done on them. I thing if You tune up SQL Server and make same table with indexes or something else You'll have same size. – num8er Jul 18 '16 at 04:21
  • also please gave detailed info about tables – num8er Jul 18 '16 at 04:22
  • I have given the exact create table query. Its pretty long. – Luke101 Jul 18 '16 at 04:25
  • o gosh (: ok... 1st varchar is not text, 2nd how You measure the weight of database tables? can You give screenshot or etc? – num8er Jul 18 '16 at 04:26
  • @Luke101, did you create a brand new database and try this, or did you possibly insert/delete/insert/delete etc.? – aquinas Jul 18 '16 at 04:51
  • I just created a new database. I got the same results. – Luke101 Jul 18 '16 at 11:04

1 Answers1

2

In SQL Server, storing data in a fixed width field takes up the same amount of space as any other value, which is the width of the field (see here).

So your SQL Server table has 100,000 values which you inserted stored as max width VARCHAR.

In MySQL, you are using a TEXT field of variable size. I believe that the data does not even need to be stored in the actual table. The storage overhead for a TEXT would appear to not be taking up as much space. This would appear to be significantly less overhead than SQL Server, which explains your observations.

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • No columns have NULLS. Every column in every row has a value. – Luke101 Jul 18 '16 at 04:13
  • You never answered my question by including your `CREATE TABLE` statements, leaving your question hard to answer. SQL Server will take up the full width because it's a fixed column. My guess is that this is not happening in MySQL for some reason. – Tim Biegeleisen Jul 18 '16 at 04:14
  • WOuld you have an idea how to fix mysql? Where would I put the TEXT field? – Luke101 Jul 18 '16 at 04:31
  • 1) he said MySQL is taking more space than Sql Server, not vice versa. 2) Varchar isn't a fixed width field. – aquinas Jul 18 '16 at 04:31
  • If it ain't broken, don't fix it. I believe if you had used `VARCHAR` with MySQL, you would have seen similar results. What are you trying to actually optimize here? – Tim Biegeleisen Jul 18 '16 at 04:32
  • Not trying to optimize but I am going to import millions of rows on this table. I have several csv files to import but if the data will increase at this rate I can't use mysql. I cant use sql server because of high cost. – Luke101 Jul 18 '16 at 04:37
  • If storage is a serious concern, then you should probably be using `TEXT`, with the caveat that it is stored off table, and therefore reads may not be as fast as `VARCHAR`. – Tim Biegeleisen Jul 18 '16 at 04:39
  • @TimBiegeleisen, I think you misread the question. He *is* using `TEXT` for MySQL, and it's WAY bigger than SQL Server is. – aquinas Jul 18 '16 at 04:44
  • @aquinas He never told us how much text he's actually storing. My guess is a ton, based on that 100x1000 is already half a GB. – Tim Biegeleisen Jul 18 '16 at 04:45
  • Well, he said "GUIDS in all columns." So we can figure 36-38 byes. – aquinas Jul 18 '16 at 04:50