What is the most efficient way to know if a MySQL longblob is empty?-CodePudding

I have a MySQL table or around 150,000 rows and a good half of them have a blob (image) stored in a longblob field. I'm trying to create a query to select rows and include a field that simply indicates that the longblob (image) is exists. Basically

select ID, address, IF(house_image != '', 1, 0) AS has_image from homes where userid='1234';

That query times out after 300 seconds. If I remove the 'IF(house_image != '', 1, 0)' it completes in less than a second. I've also tried the following, but they all time out.

IF(ISNULL(house_image),0,1) as has_image

LEFT (house_image,1) AS has_image

SUBSTRING(house_image,0,1) AS has_image

I am not a DBA (obviously), but I'm suspecting that the query is selecting the entire longblob to know if it's empty or null.

Is there an efficient way to know if a field is empty?

Thanks for any assistance.

CodePudding user response：

I had similar problem long time ago and the workaround I ended up with was to move all blob/text columns into a separate table (bonus: this design allows multiple images per home). So once you've changed the design and moved the data around you could do this:

select id, address, (
    select 1
    from home_images
    where home_images.home_id = homes.id
    limit 1
) as has_image -- will be 1 or null
from homes
where userid = 1234

PS: I make no guarantees. Depending on storage engine and row format, the blobs could get stored inline. If that is the case then reading the data will take much more disk IO than needed even if you're not "select"ing the blob column.

CodePudding user response：

It looks to me like you are treating the house_image column as a string when really you should be checking it for NULL.

select ID, address, IF(house_image IS NOT NULL, 1, 0) AS has_image
from homes where userid='1234';