BLOB as Primary Key?

Shawazi

Is there a way to make a blob a primary key? I want to use that to avoid double posting by hitting refresh.

ahundiak

Nope.

mattr

Take a look at the inserted time and if it is really, really close to the previous one then ignore it.

Sxooter

Sorry, but using something as large as a blob as a pk is not a good idea, as it will create a HUGE index that runs slow, so even if you could, you shouldn't.

Here's what you do:

use some quick checksum program (md5sum would work, but it can be slow, look for a faster algo, I think there's one or two built into PHP) and store that in a pk field along with the blob in the same table, and pk the hash field.

csn

Yeah, I've seen some apps (ikonboard is one I think) that use 32 byte varchar fields as primary keys. I think they're md5 hashes.

I've never had a reason to use anything other than int4's.

Sxooter

Ints will always be faster. Note that rather than treating the md5 sum as a pk, you could still use some other field (autoincrement, or fk etc...) as your pk, and then just create a unique index on the md5 field seperately.

I'd probably do it that way.

mattr

I like the idea of using the MD5 as a double-post indicator. I forgot that vBulletin does the same thing for attachments.

Keeping them around, though, can be a big space hog. Imagine 5 million posts at 32bytes each == 160MB + ~160MB = 320MB.

Maybe you can have a temporary post hash table that stores the posthash and datetime, and compare a new post to the posthash table where datetime < now - 10seconds or something.

Sxooter

Then write an insert trigger that deletes all the entries in the table that are over say a day or two old. That way everytime you add a new article, all the old ones get flushed out. Since the table would stay small, it should run pretty quick with proper indexing.

This whole thread has me feeling like a mad scientist. 🙂

tasistro

Here is what I'd do.

Create a row and return the ID (primary key say an int or bigint depending on expected size). Set all fields to blank except status (status is the smallest possible size, tiny int). Set status equal to day of month % 3 + 1

Insert the ID into the form that will be posted to with the data.

Wait for user input.

Check for status == -1. If status is -1 then ignore post.

else

Upon user post update record with blob data and set status -1. Doing so locks the record.

At just past midnight flush all records with status == (day of month-2) % 3 + 1.