-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Stefan, et al --

...and then Stefan Hinz, iConnect (Berlin) said...
% 
% Dave,
% 
% > How does one decide whether to define a table with varchar() or char()
% > field definitions?
% 
% Use CHAR, if you don't have to care about disk space, and if speed is your

I don't think I should have to; at maybe 1k clients with a couple GB of
space for the DB, that should give me 2MB of data space per client, which
is pretty ridiculous for what we have planned.  Speed would be much more
valuable (actually, it always will, and if the system gets big enough to
outgrow the little box on which I'll throw it for starters I'll have
money for applying many of the suggestions seen elsewhere on this list),
so it sounds like fixed-length is the way to go this time.


% first goal. Using CHARs with lots of spaces in the data can be
% counterproductive, though, because MySQL will have to return big result
% sets.

Hmmm...  So it's a good idea, naturally, to limit my char() size to the
biggest reasonable requirement rather than throw allocations around like
crazy; that makes sense.


% 
% > In this case, I'll have names, phones, email, res and mailing addresses
% > fields (all of which are relatively short and could be fixed-length) as
% > well as a larger TEXT field for general notes.
% 
% One thing you might want to do is store the fixed-length fields in one
% table, and the variable-length fields in another table, with an ID as
% reference between them. TEXT will always be of variable length.

OK.  Actually, I don't think I'll have many fixed-length fields...  We
use 10-digit dialing here, so the phone numbers will be, but then again I
have to leave room for an extension on the work number...  The zip codes
are fixed, but these are both pretty short things.  The names and addrs
will be variable, so I could just pad them out to 25 chars and eat it for
"Jan Doe".

Is it considered rude to post one's database schema, or even a single
table's definition, for advice?  And, on the other hand, is it considered
dumb to expose one's schema because it reveals information that could
make hacking easier?  [I'm not a fan of security through obscurity, but
there are certain things that one never tells :-]


% 
% > As a side note, I'd be interested in a high-level reading of how data is
% > stored in MySQL databases and how compacting and optimization is done...
% > It would have to be an "executive summary" overview so that it didn't my
% > eyes bleed, though :-)
% 
% Why don't you start here:
% http://www.mysql.com/documentation/mysql/bychapter/index.html#Column_types
% ;-)

Heh :-)  Well, I've been reading section 6.2 quite a bit, but maybe not
enough.  I was looking for something like "when a row with a variable
field grows, it's rewritten at the end of the database file and the hole
is later reclaimed for a smaller record" (and perhaps "holes are compacted
and the database compressed automagically") or, conversely, something like
"expanding the first row in your database requires rewriting the entire
rest of the file, so only use varchars on small datasets or when old rows
will not be updated" or the like.


% 
% Regards,
% --
%   Stefan Hinz <[EMAIL PROTECTED]>
%   CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de>
%   Heesestr. 6, 12169 Berlin (Germany)
%   Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


Thanks a bunch! & HAND

mysql query,
:-D
- -- 
David T-G                      * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://www.justpickone.org/davidtg/    Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE9/Uc+Gb7uCXufRwARAvKBAKCbEA5RkkLj77JL1RKm3oX06T+CMgCeMqJg
T4fVuh9AG9WJKyIqGrhmQsM=
=/pAJ
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
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

Reply via email to