-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Benjamin, et al --
...and then Benjamin Pflugmann said... % % Hi David. Hi! % % On Sun 2002-12-29 at 16:21:56 -0500, [EMAIL PROTECTED] wrote: % > ...and then Benjamin Pflugmann said... % [...] % > No; everything is one hour. Two hours is two bookings. % % Ah. Good. That makes the case easier and is exactly the case my last % answer was about. OK. % % [...] % > % know beforehand when the class starting at x o'clock will end? And % > % saving a number for the time slot would also work? % > % > Saving a number? I don't know what you mean here... % % Not important anymore. Ah; good :-) % % But for completeness: You could do as with school classes. 9-10 % o'clock is 1st, 10-11 is 2nd and so on. So you could theoretically % save "date 2003-02-10 class 1" instead of "date 2003-02-10 time % 09:00:00". Oh, I see... I've been thinking about that, too; I currently use a datetime field, but all I really need is a date and an hour. I figure with one combined field I can match against XXXX-YY-ZZ% but maybe two fields would make it easier to pull out the time column... % % Not that my suggestion would be better necessarily, it just served to % make it less ambiguous what we are talking about. Seemed it failed. ;) Hah :-) Well, thanks, anyway! % % [...] % > % In the case of time slots, you could create a unique index on % > % (client,time) and (instr,time) and skip the selects. Simply insert and % > % check for an duplicate key error. % > % > Hmmm... OK; avoiding duplicates is the end goal and so I should probably % > learn more about this apparently simple approach. % % Yes, the main purpose of UNIQUE indexes is to guarantee uniqueness in % a column (or a set of columns). So that's the way to go. Oh, I see! Cool. Now I presume that I will want a unique index for (instructor,timeslot) and a[nother] unique index for (client,timeslot). I can't do a unique index on the place because some rooms are booked for groups (I'm planning to create 12 records for a 12-seat class when it's defined, and then I'll update those records until there are no more with empty client values as I add students). Do I want two unique indexes like that, or some other approach? % % > Um, what next? :-) % % :-) Hee hee. % % > This is probably in the mysql doc, so a pointer would be appreciated % > but know that I will [try to] look up multi-column indexes and see % > how they deal with duplicates. % % http://www.mysql.com/doc/en/Multiple-column_indexes.html Cool; thanks. % % Although the manual covers a lot of stuff which is not strictly % MySQL-related, it is not well suited to be an introduction into the % world of relational databases. Yeah. I've been drawing heavily on past theory classes, but that's now ten years old :-) % % May I suggest Paul DuBois' book? It provides a lot of insight in such % issues. You may. If you know anyone who wants to buy it for me for a belated Christmas present, you can suggest that, too :-) OK, OK... Having found out about it, I think I will probably go and get it; it sounds like a good investment. Even if I start with a borrowed copy from a library I want to get my hands on it. % % [...] % > I figured I'd have to check every record to see if the datetime and ... % > many of them), and maybe not with the indexes (hmmm... indices?) you % > mention above. % % Well, that is what indexes are all about. Searching for 1 record in OK... Neat. % 1 billion with indexes can be as fast as a full table scan for 1 in 30 % records. Good :-) % % A more complex answer from some ealier posts of mine (still % simplified): % % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:77486 % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115591 Thanks; I'll read them next. [Actually I just popped out and read them.] Oh, I get it; an index is a fast BTree pointer to the data; it's a hash table! Cool. Yes, that's what I want, all right. % % > I don't know exactly, but we're probably looking at a total of 3k or 4k ... % > that a few or a lot? % % It depends on how many time slots you have. The general answer is that % you want the column with the highest cardinality first. But also the % storage size matters and so on. That's what "etc" was about. ;-) Heh :-) % % Usually I use the trial and error method I mentioned below, myself. OK. % But sometimes it is so obvious that you don't have to. Like if you had % only 40 time slots (only planning a week ;). The client should come % first. Yeah. I won't have that few; I'll have about 80 per week (6a thru 7p on six days) for every week from now until the end of time (hey, I can hope that I'll be making money from this software for that long!). So in the beginning I'll have fewer time slots, but by the end of the first year that will very probably have flipped... Good grief; what do you do in that case? Well, I suppose it's an opportunity to charge for an annual tuneup ;-) % ... % > I don't even know; see my newbie comment above :-) I thought that a % > temporary table helps to speed up specific queries because the grouping % > is already done and then you just select from that, but I dunno :-) % % That is correct. *If* you need grouping. And then, as you said, only % for some specific queries. Oh :-) How nice of me to answer my own question. Now if only I knew what I said... :-) % % But you don't (need grouping). You are right, that you have kind of OK. % groups in the table. But the term "grouping" is about when you want to % retrieve some fact based on groups. E.g. if you want to know the next Ahhh... % class each client attends, similar to this: % % http://www.mysql.com/doc/en/example-Maximum-column-group-row.html Thanks. % % % But I think the overall answer is: Don't optimize early. You are % right, that you need a clean design to get reasonable speed. I suggest % you first care about that and think about specific optimizations (like % using temporary tables) only stumble upon a specific bottleneck. It Ahhh... % sounds to me as if you are worrying about non-issues currently. But, % of course, a basic understanding of what is going on never hurts. Heh. Yeah :-) Where can I buy one of those? ;-) % % HTH, and yes, HAND, too, % % Benjamin. % % -- % [EMAIL PROTECTED] Thanks again & HAND & HH :-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) iD8DBQE+EYPaGb7uCXufRwARAl5ZAJ4mrgE3IMH6/m5ZHss5CCvWBUZ01ACfSeTB bwCfRTalr0aQdeCZNsrmIzQ= =8Wh/ -----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
