IDK if I'm really allowed to ask this, but...

notHeidi's profile image notHeidi posted 2 years ago in General Permalink

If you use Heidi to copy a large table to another database, using the "Export database as SQL" option, you can see the various SQL calls it makes - including the ones that split the table into chunks - ie ending ...LIMIT X ...LIMIT 2X, X ...LIMIT 3X, X ...etc but I cannot for the life of me figure out how it calculates the value of X. I presume it must be some combination of table size and/or average row size and/or max_packet size (maybe), but I can't see from the SQL calls logged, how the information there is used to obtain the value for X that is shown.

Of course there's no obligation to answer this, but I am intrigued by this...

ansgar's profile image ansgar posted 2 years ago Permalink

Good question indeed, as I already spent quite a few hours just for that calculation, especially making it compatible to the non-MySQL connections.

HeidiSQL fires such a query for getting a list of objects in a database: SHOW TABLE STATUS FROM mydatabase;. The result contains a value "Avg_row_length" which is for the estimation. Then, HeidiSQL has a hardcoded limit of 100MB per result. The calculation then is as you might already suspect:

Limit = 100 MB / Avg_row_length

There are some additional things, like when Avg_row_length is 0, for various reasons. But these are just for the non-standard cases.

notHeidi's profile image notHeidi posted 2 years ago Permalink

Thanks!

Please login to leave a reply, or register at first.