MySQL Forums
Forum List  »  General

Horrible performance on BLOB's when using WHERE or ORDER BY
Posted by: Arbol Arbol
Date: September 04, 2010 09:45AM

Hello everyone,

I'm not a newbie at MySQL but this forum seems more active so I posted here.

I'm having a horrible time figuring out what's making this query so slow:
SELECT search_page_id, url, UNCOMPRESS(html_compressed) FROM search_index WHERE random > 4000000000;

That's 4 billion. My random column is an INT and only about 5% of the rows in the table meet the WHERE statement. The table has 37 million rows and is 200 GB's. Obviously, this is not a query I expect to run immediately. However, what's scary to me is that a full table scan is many times fast than the WHERE statement. In other words, this query:
SELECT search_page_id, url, UNCOMPRESS(html_compressed) FROM search_index;

Finishes MUCH FASTER than the WHERE statement. Understand that this means it's faster to decompress 37 million BLOBs and pull them from the DB, than it is to decompress 3 million.

The only theory I can bring to this is that it forces the disk to access the BLOB's at random for some reason (which would destroy disk performance). I've noticed when it runs the full SELECT (with no WHERE), the disk IO is about 40 Mbps - but it falls to around 1 Mbps when I add the WHERE.

I'm running some more numbers now to try and understand the root cause. Does anyone have any other theories?

Thanks in advance!

Options: ReplyQuote

Written By
Horrible performance on BLOB's when using WHERE or ORDER BY
September 04, 2010 09:45AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.