Hi David. 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. [...] > % 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. 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". Not that my suggestion would be better necessarily, it just served to make it less ambiguous what we are talking about. Seemed it failed. ;) [...] > % 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. > Um, what next? :-) :-) > 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 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. May I suggest Paul DuBois' book? It provides a lot of insight in such issues. [...] > I figured I'd have to check every record to see if the datetime and > client (or instructor) matched what I have already, and a few years > from now when that table is long it seems like that would take a > while (seconds? minutes? i dunno) to query... Maybe not at all on > such a simple table (very short fixed-length fields, and not very > 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 1 billion with indexes can be as fast as a full table scan for 1 in 30 records. 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 [...] > % If not: Whether an index (client,time) or (time,client) is better > % depends on your data (few or a lot of client, etc). If you don't have > > I don't know exactly, but we're probably looking at a total of 3k or 4k > clients with some .5k actively making frequent bookings, and up to a > dozen instructors (per installation, which for starters will be a little > PII-266 with only 64M of RAM and a 5G IDE disk but will probably settle > on a cheapo PIII-800 with 128M and a 10G IDE disk at each studio). Is > 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. ;-) Usually I use the trial and error method I mentioned below, myself. 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. > % I you are not sure, simply build all of them and look which one get > % used. Then dropped the other ones again. The same applies for "instr". > > That's a safe approach that should get the questions answered :-) Jupp. [...] > 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. But you don't (need grouping). You are right, that you have kind of 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 class each client attends, similar to this: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html 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 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. HTH, and yes, HAND, too, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- 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
