Export Large Table

HeidiSQL 12.2.0.6576

I have a large table (7.5GB; 1.3 million rows; fixed rows, around 5952 bytes). When I try to export this table with data (either to zip or directly to another sql server), Heidi tries to fetch entire table, instead of splitting everything into chunks (Max INSERT size doesn't work). And cancel button doesn't work either, cause entire app is hang up on the current query.

If I were to guess, the problem for Heidi is my primary key (it uses 2 columns).

It consists of (in specified order):

1. Date.
2. Int.

Do you think you can fix it?

Please watch out for the SELECT query in the log panel when you export that table. HeidiSQL limits the number of rows by calculating chunks based on the average row length of the table.

I just ran a test on a 700MB table with 1.5 mio rows, and HeidiSQL uses a limit:

In my case it tries to fetch many many times more that it should.

Yes that looks indeed strange. I suppose the average row length is very small, so the calculated row number is too high.

You could check that with such a query:

SHOW TABLE STATUS LIKE 'reporttag_opt';

Please post the results here, especially the avg_row_len.

Name;Engine;Version;Row_format;

reporttag_opt;MyISAM;10;Fixed;

.

Rows;Avg_row_length;Data_length;Max_data_length;

1345793;5950;8007468350;1674776111428403199;

.

Index_length;Data_free;Auto_increment;Create_time;

18138112;0;\N;2022-11-24 15:56:26;

.

Update_time;Check_time;Collation;Checksum;

2022-12-02 10:55:44;\N;utf8mb4_general_ci;\N;

.

Create_options;Comment;Max_index_length;Temporary

row_format=FIXED;;4398046510080;N

Thanks. I will look at the calculation code after work.

The current limit was calculated with the formula 100 * 1024 * 1024 / Avg_Row_Len. In your case, Avg_Row_Len was 1, resulting in a LIMIT 104857600. It's "1" likely because you have disabled the session setting "Get full table status", right?

Next builds assume a default Avg_Row_Len of 10000 bytes, so without the full table status you will get a limit of 10486, which should perform much more responsively.

It's "1" likely because you have disabled the session setting "Get full table status", right?

No. I never even touched that tab. And this option is currently checked.

I have posted my data. Avg_Row_Len equals 5950.

And yeah, it works after last update. Thanks.

Hi this is Gulshan Negi I think the number of rows is too much, you need to recheck your query and run it.

Thanks