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

Question

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.

Is your table indexed? That could be causing all your problems. Also, it probably makes sense to split your images to a separate table. — Andy Lester, Mar 29 '22 at 19:59
Hi @AndyLester, I agree that the images should have been in a separate table. I've just inherited this design. — DBtake3, Mar 29 '22 at 20:08
@nicael, good point. I've thought of that too I was hoping to not add another index. It just seems like it would be quite large considering it's an index of longblobs. — DBtake3, Mar 29 '22 at 20:10
You can't index a longblob (unless you use a prefix index). I think the point would be to index `userid` so it only needs to examine the subset of rows that match userid=1234. You didn't post a description of your table or any current indexes, nor did you provide the result of EXPLAIN for your query, so we can only guess that your query is doing a table-scan. — Bill Karwin, Mar 29 '22 at 20:16

Salman A · Answer 1 · 2022-03-29T22:20:21.643

1

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.

edited Mar 29 '22 at 22:20

answered Mar 29 '22 at 20:56

Salman A

262,204
82
430
521

I would use a left outer join instead of the correlated subquery. – Bill Karwin Mar 29 '22 at 21:44
That would duplicate homes that have more than one image. – Salman A Mar 29 '22 at 21:52
The task is to find those with no images. An outer join and then selecting where the images is NULL would return only one row per home. – Bill Karwin Mar 29 '22 at 22:07
Apparently OP wants a `has image` flag for each row, not filter such rows. – Salman A Mar 29 '22 at 22:08

score 0 · Answer 2 · answered Mar 29 '22 at 20:00

0

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';

answered Mar 29 '22 at 20:00

Andy Lester

91,102
13
100
152

Thanks @andy-lester, I just tried that and unfortunately it also times out. – DBtake3 Mar 29 '22 at 20:12

O. Jones · Answer 3 · 2022-03-30T11:14:47.533

LONGBLOBs can be indexed in MariaDB / MySQL, but the indexes are imperfect: they are so-called prefix indexes, and only consider the first bytes of the BLOB.

Try creating this compound index with a 20-byte prefix on your BLOB.

ALTER TABLE homes ADD INDEX user_image (userid, house_image(20));

Then this subquery will, efficiently, give you the IDs of rows with empty house_image columns.

               SELECT ID
                 FROM homes
                WHERE userid = '1234'
                  AND (house_image IS NULL OR house_image = '')

The prefix index can satisfy (house_image IS NULL OR house_image = '') directly without inspecting the BLOBs. That saves a whole mess of IO and CPU on your database server.

You can then incorporate your subquery into a main query to get your result.

SELECT h.ID, h.address, 
       CASE WHEN empty.ID IS NULL 1 ELSE 0 END has_image
  FROM homes h
  LEFT JOIN (
               SELECT ID
                 FROM homes
                WHERE userid = '1234'
                  AND (house_image IS NULL OR house_image = '')
       ) empty ON h.ID = empty.ID
 WHERE h.userid = '1234'

The IS NULL ... LEFT JOIN trick means "any rows that do NOT show up in the subquery have images."

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

3 Answers3