BLOB or MEDIUMBLOB?

[Zaphod]

Brian, I'm wondering about how big a feed might get, even after mysql compress. If you try to compress and insert a value greater than the max size of the field then I imagine that the sql insert will fail. That's not so bad, it just means that the cache failed on insert.. which just means that it won't be there when you look for it again, and you'll have to pull a fresh feed from the API. The sad part is that this means that you'll never cache the biggest feeds haha :)

I'm wondering... is there a major performance hit in using MEDIUMBLOB instead of a BLOB? I think a BLOB tops out at 65k but a MEDIUMBLOB goes to nearly 17 megs... 17 megs is WAY bigger than a feed is likely to get :D but it could easily get over 65k compressed. I wish there was a SLIGHTLYLARGERBLOB that would to to 200k :p

I know you're busy, but I figured you would likely know more about this than anybody else I know. If there are Gods of MySQL, then you're their prophet :D

[Keen]

Thats pretty interesting to know as well, but I would think there wouldn't be that much performance difference. But I'm curious to hear brians view as well =)

[AcidRaZor]

AFAIK the insert won't fail, it'll just insert a truncated version. With MS SQL it will fail, MySQL won't. Not sure if they fixed that yet. Annoying when I came across it.

As for the BLOB/MEDIUMBLOB. (also AFAIK) it won't use up 17mb for a 200k feed. It'll use up how big the feed is. If your RAM can't handle it your Disk I/O is going to be the bottleneck, especially if you go up to 17mb

[Zaphod]

Yeah, I'm not expecting any feed to ever get to 17 megs haha. I'm just wondering if there's a performance disadvantage to moving to it. Just like TEXT and LONGTEXT... I'm not sure if using the larger container inherently uses more resources.

[prosperent brian]

Blobs are stored differently in innodb. If it doesn't fit inside of a 16K page, the rest is stored on disk and cached separately, so it definitely can impact performance negatively. I'll post up more detailed info tomorrow.

[AcidRaZor]

*edit*

Blobs are stored differently in innodb. If it doesn't fit inside of a 16K page, the rest is stored on disk and cached separately, so it definitely can impact performance negatively. I'll post up more detailed info tomorrow.

Didn't know that :)

[Zaphod]

Blobs are stored differently in innodb. If it doesn't fit inside of a 16K page, the rest is stored on disk and cached separately, so it definitely can impact performance negatively. I'll post up more detailed info tomorrow.

Oy, glad I asked!! Thanks! :)