On Fri, Aug 24, 2001 at 09:59:38AM -0400, Tac/Smokescreen wrote:
>
> I have a large (>40 million rows) table that currently consists of a
> few integers and a varchar. I need to add a memo (text > 255 chars)
> field to about 2% of the rows. Originally I was going to create
> another table with a key and the memo field, but this method is
> complicating the programming logic. The easiest solution would be
> to add the memo field right to this table, and use it only when
> needed.
Agreed, that is probably the easiest.
> Does the presence of a memo fields affect the performance a lot?
Depends on what "a lot" is for you. There's a chance that you won't
even notice. Many of them are going to be empty, so you'll likely
only notice when you retrieve records that do have a chunk of data
with them.
> Are memos stored with the records themselves, or in a separate part
> of the table data (I believe Access and SQL-Server stores the memos
> separately, which can kill performance).
They are stored together.
> Finally, in this case should I allow Null's and use them for the 98%
> empty fields?
Either you allow NULLs and use them, or you'll need some magic value
in your application so that you can detect the "null" fields.
In theory, having NOT NULL columns is supposed to make MySQL a tiny
bit faster, but I've not found the difference to be noticeable. It
shouldn't be too hard to measure if need be.
I do, however make a habit of using NOT NULL simply because I think it
makes for a more sound schema in most cases.
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 9 days, processed 114,953,823 queries (140/sec. avg)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
