CODEWITHSUNDEEP
mysqlblobquery-optimization
DBtake3
DBtake3

Reputation: 126

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

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.

Upvotes: 2

Views: 1581

Answers (3)

O. Jones
O. Jones

Reputation: 108796

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."

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272266

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.

Upvotes: 1

Andy Lester
Andy Lester

Reputation: 93745

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

Upvotes: 0

Related Questions