[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[ale] Database consulting in return for homebrew?
On Sun, 7 Dec 2008 08:58:33 -0500 (EST)
Mike Harrison <meuon at geeklabs.com> wrote:
> Use a "unique" record identifier on every table.
> I name all of mine: "uniq"
Do, however, use a natural key instead of a surrogate. Using an
auto-incrementing integer or a UUID is fine for some things, where you
don't have an a priori natural key or where you've denormalized for
performance and your natural key is spread amongst a few different
tables. But, if you have a natural key present, use it.
> Make sure any number fields (like a double(16,2)
> will handle the number size needed, to the accuracy
> required. "unsigned" is helpful, but if you need to
> work with negative numbers, a pain.
MySQL doesn't have an arbitrary-precision field type? It is of course
slower to use AP numbers, but sometimes you just don't have the ability
to constrain a numeric field based on what might get put into it in the
> "The FLOAT and DOUBLE data types are used to represent approximate
> numeric data value"
> That word "approximate" is important. MySQL will round
> numbers to fit. I often do math to 6 decimal palces
> in MySQL and this will bite your ass.
Sounds like MySQL still needs to gain NUMERIC support. *shrugs*
> Index, index. index. Can sure speed up things
> as they get larger.
Do be sure not to index fields that have fields of a very limited
range. For example, indexing an ISO 5218 gender code field is pretty
pointless, unless you create a subsetted index on values that are
neither 1 or 2. Not sure if you can do that in MySQL or not, in
PostgreSQL, this is called a conditional index---very powerful and nice
to have when indexing is otherwise suboptimal. Definitely don't index
if there are only a few values in the column ever, because unless
values are widely distributed, there will still be a table scan
required. Try not to rely on columns with low distribution of values.
My sigfile ran away and is on hiatus.
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 197 bytes
Desc: not available
Url : http://mail.ale.org/pipermail/ale/attachments/20081208/e93a66c0/attachment.bin