Selecting Blobs

[Traian Dragomir]

Hi. I am a recent SQLyog Community user, and your software really made me a happy man.

But now i have to administer a MySQL database which stores images in a table as medium BLOBs. And after doing it on localhost and over LAN, now i have to do it remotely over the Internet. And here i started trembling… as the table data never finished loading on the client.

SQLyog downloads everything on the client, and then displays the data… but when using it with BLOBs over the Internet it's killing me.

Would you consider adding a feature (an extra option) to display the size of the BLOB when selecting all the data fields from the table, but not loading the actual BLOBs until the user specificaly asks to view / add / edit the BLOB for a specific row ?

This feature would help me a lot, and probably could help lots of other people in my situation.

The only GUI for MySQL that has such a feature is PHPMyAdmin, it doesn't bring the actual BLOB when displaying the fields in a table, but brings back only the size of the BLOB or NULL. But PHPMyAdmin takes so much time to reload pages and is only my last resort if i can't deal with this issue any other way…

I'm not asking for SQLyog to have this as a default feature (some people might want to have a minimum number of database hits), but it would be awsome if i could find an option to activate such a mode that brings the BLOB only when i request to view it by double-clicking the specific BLOB cell…

[Sayan Chaliha]

Hi Traian,

This forum is for MONyog — MySQL Monitor.

All SQLyog queries should be posted in the SQLyog forum, and I will move this thread there.

Thanks, and I'm sure someone will get back to you at the soonest.

[peterlaursen]

What SQLyog version are you using?

[peterlaursen]

The reason why I ask is a change in 8.15 as described in changelogs: 

“Improved the memory handling in GRIDS with BLOB/TEXT columns. This fixes some slugginess when scrolling a GRID containing such data.”.

We will still fetch the data from all rows of the table, but the experience when working with the program will be more smooth and pleasant than before this change.

[Traian Dragomir]

“Improved the memory handling in GRIDS with BLOB/TEXT columns. This fixes some slugginess when scrolling a GRID containing such data.”.

I'm using the latest version (i guess, downloaded 2-3 days ago).

I'm not talking about the scrolling delay… i'm talking about loading a table with several row, where each row has a BLOB field with a 300 – 500 kb image. When loading the table data, it has to download 500 kb x 200 images (they're actually more then 200) , that means loading 100 MB over an internet connection before displaying the data.

I wanted an option to download the data without actually downloading the BLOBs when i display the table contents (just the BLOB size that you already show) , and to fetch the BLOB for a cell only when i double click on that cell.

This way it should only load 200 or more rows of text, then display the table data, and just download the BLOB for a cell only when i ask for it. Downloading BLOBs from the beginning along the text attributes makes it impossible to display more than 5 or 10 rows, the download time would be criminal.

I tried to “trick” my MySQL database using a view in which i used a case statement that would only display LENGTH(myBLOBcolumn), but i need to update data in the table and be able to insert BLOBs as well, and that case statement makes my view impossible to edit/insert…

[Traian Dragomir]

I just noticed that HeidiSQL v5.0.0.3031 does this. They use SELECT LEFT(BLOBcolumn, 256). And it displays my table quite fast. Then, when i ask to view/edit/insert the BLOB for a cell, they do that separately.

For now, HeidiSQL saved me from using the time-consuming PHPMyAdmin.

I had trouble with HeidiSQL's export/import features, messing up one database, that's how i got to SQLyog which worked like a charm. I would love to have an option in SQLyog to solve my BLOB issue as well.

Either way, anyone who spends day after day creating such a great tool and giving out a free version of it has my greatest appreciation.

Many thanks!

[Author]

Posts