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