Need help with a mySQL database

[Rickster]

Hi all, does anyone know why the DB on my server reports that the database is 106GB via phpMyAdmin and via mySQL workbench but when I download the DB from the server using mySQL workbench it reports the DB as being 86GB.

I need to know if the DB on the server and my drive are the same. How would I compare it, I was thinking using MD5 or SHA1 hashes to compare them but I can't see this option on the server.

Ive also downloaded it twice and they are both 86GB to they byte.

 

[Giovanni]

Are you selecting any compression when downloading?

 

[Rickster]

Are you selecting any compression when downloading?

Doesn't look like it.

 

[arf]

holy moly my English is atrocious. thanks Asia.

Most probably due to the indexes created on your server and not in the data dump.

 

[Rickster]

holy moly my English is atrocious. thanks Asia.

Most probably due to the indexes created on your server and not in the data dump.

Sorry I'm not too clued up on this stuff, what are you trying to say in layman's terms.


Thanks.

 

[arf]

On your tables in your database there are objects called Indexes. Indexes basically sort your data and assign them "keys" so that queries run much much faster.
These objects (indexes) take up disk space.
You are doing a data export, so you are only exporting out data, not objects like the indexes. (This is my guess, not too clued up with mySQL)

 

[Rickster]

On your tables in your database there are objects called Indexes. Indexes basically sort your data and assign them "keys" so that queries run much much faster.
These objects (indexes) take up disk space.
You are doing a data export, so you are only exporting out data, not objects like the indexes. (This is my guess, not too clued up with mySQL)

So if I take my DB then upload it would it make indexes by itself over time?

 

[Giovanni]

So if I take my DB then upload it would it make indexes by itself over time?

Yep. You can create your own indexes as well though. SQL CREATE INDEX Statement Good idea if you are querying using some key frequently.

Sounds like a solid reason for the size difference.

 

[arf]

So if I take my DB then upload it would it make indexes by itself over time?

Indexes won't create over time:
1. They have to be explicitly be created.
2. When you create a table, it will have a Primary key, an index automatically gets created on this column.

Sop yeah, you take a download of your DB. Restore it, it will create the tables, insert data, create indexes and it should then match the size once restored on your drive.

 

[Bird87_ZA]

phpMyAdmin doesn't always report table sizes correctly.

But it could also be that the tables hasn't been Optimized in a while.

EDIT: Just to confirm to myself that I am not deur die kak, I optimized all tables on my 75.3MB local test table, and it went down to 74.7. So on a 160GB table, it might go down a lot.

 

[lowracer]

I don't currently use mysql, so anything I say might be wrong.

Higher reported space might also be due to slack space, example deleted records, space being kept for a "maximum size" record, that isn't full, etc

for example - if you delete a record - it will just mark the record as deleted, and keep the space.

the second - say you have a column that can be 250 characters, but you only use 10 characters - when you back up - the back up discards the blank space - and is therefor smaller. but the space is kept on the database - so if I update the column with a new value, say 50 characters long, it writes the 50 characters in the space provides (and still have an extra 200 characters available) If it didn't do that - it would have to move the record to a new position, mark the old one as available, update the indexes, etc. it could also reserve space in the record based on a fill factor, which allows it to "grow" until the record's total length is more than the allowed space, in which case it would do the musical chairs thing.

 

[Cyst]

If you do an export from mySQL using phpMyAdmin or Workbench, isn't the file it creates just a bunch of SQL statements to create the tables and insert all the rows (and whatever other keys and whatnot)?
That's not actually a database, it's a SQL script to recreate everything that was in the original database.

When you import that file and it creates all the stuff, the resulting database might end up being similar in size to your original one.

 

[Sub-lime]

I'm going with ARF's answer. While you're at it change to mySQLi if you aren't already.

 

[SantaMuerte]

If you do an export from mySQL using phpMyAdmin or Workbench, isn't the file it creates just a bunch of SQL statements to create the tables and insert all the rows (and whatever other keys and whatnot)?
That's not actually a database, it's a SQL script to recreate everything that was in the original database.

When you import that file and it creates all the stuff, the resulting database might end up being similar in size to your original one.

This is correct. It exports a script like when you generate a script in SQL Server.