Very slow data browsing on tables with long BLOBs

I have one table which contains cache of pdf files. The files are stored in a MEDIUMBLOB column and the problem is that heidisql doesn't use LEFT(col_name, 256) on these fields like on TEXT fields. When I click on the Data tab this query is issued:

SELECT `product_id`, `lang`, `domainname`, `created_time`, `script_duration`, `pdf` FROM `mydb`.`cache_product_pdf` LIMIT 0, 1000;

If the pdf data size is roughly 40KB each then this results in transferring 40 KB * 1000 = 40 000 KB = 40 MB of data over the internet and the program becomes unresponsive for quite a long time. I don't know if blobs are not truncated on purpuse but in most cases I don't need the whole blobs downloaded when browsing a table. And if my blobs were even larger then the amount of data to transfer could become astronomic.

Not reproducible here. Please post your CREATE TABLE query here. I guess there is some length specification on that MEDIUMBLOB column is it?

No length specification, here is the sql:

CREATE TABLE `cache_product_pdf` (
`product_id` MEDIUMINT(8) UNSIGNED NOT NULL,
`lang` CHAR(2) NOT NULL COLLATE 'utf8_polish_ci',
`domainname` VARCHAR(64) NOT NULL COLLATE 'utf8_polish_ci',
`created_time` DATETIME NOT NULL,
`script_duration` FLOAT NOT NULL,
`pdf` MEDIUMBLOB NOT NULL,
INDEX `product_id` (`product_id`)
)
COLLATE='utf8_polish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

Oh, the forum garbles the output, maybe this time it will look better:

CREATE TABLE `cache_product_pdf` (
`product_id` MEDIUMINT(8) UNSIGNED NOT NULL,
`lang` CHAR(2) NOT NULL COLLATE 'utf8_polish_ci',
`domainname` VARCHAR(64) NOT NULL COLLATE 'utf8_polish_ci',
`created_time` DATETIME NOT NULL,
`script_duration` FLOAT NOT NULL,
`pdf` MEDIUMBLOB NOT NULL,
INDEX `product_id` (`product_id`)
)
COLLATE='utf8_polish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

There is number 8 in parenthesis in the first MEDIUMINT specification, not a smiley! :)

Got it: This table does not have a primary or unique key which is required for editing purposes. When no such key is available, HeidiSQL ensures you have all rows without length limit, so UPDATEs and DELETEs can be generated safely. In your table it looks like you should add some INT UNSIGNED AUTO_INCREMENT column.

Okay, I see what you mean. Adding a unique column would be an option, however I think it would be good if heidisql could handle such cases well, I can think of a solution:

Make updates and deletes disabled for tables with BLOBs, TEXTs and without a unique column for the sake of fast data viewing. This could be optional and even turned off by default if not everyone likes it.

You see, I could add a unique column but:

1. In this case I have absolutely no need for a unique column other than to satisfy heidisql. I have a PK column on most tables but this is just a table for cached data and there is no need for a PK, it would only take up more memory.

2. Sometimes I may use heidisql for viewing databases which were not created by me and I am not supposed to change tables. Then I am in for an unpleasant surpise when heidisql hangs for long minutes when I just want to have a look at the data. I don't mind about deletes and updates in such cases.

This is just a suggestion, maybe you can find another solution. I was just surprised at first when phpmyadmin displayed the data immediately while heidisql took so long, I thought something was not right :). Of course, PMA was installed on the remote server so it didn't get the overhead of network transfer.

It was a highly starred feature request that such tables should get editable in the data tab, as that was not the case in earlier versions. I don't recall the issue id right now but I know there was quite a long discussion about this point. The consensus at the end of the discussion was that it's ok for most users to have no LEFT() limitation on tables without PK/UK. I don't think I'll change this stuff again or add a preference option here.

If there was a long discussion then I think it's a good candidate for a preference option. But if you say no then I'll have to live with that.

The most important things before you start is to back up all your files of the old device. Speaking of files lost and recovery, app file manager is a great choice for you.