Okay, then I'll go through it point by point :)
> Thanks. I've read the manual. I guess I was looking for a more direct > explanation to make sure I had it clear and to learn any tips that might > be useful that wouldn't be in the manual. As my database will be quite > large I'm worried about effective optimizations. > > > > Can anyone give me some tips on how indexes work? > > > > > > I noticed that UNIQUE() seems to create a lock on all given fields per > > > call like UNIQUE (md5, mime) so that no row can have the same > combination > > > of md5 and mime type which is good but assuming I wanted to have each > > > unique on it's own I'd need UNIQUE (md5), UNIQUE (path) right? > > > Yes, that's correct > > > Are INDEX's the same as KEY's? When I INDEX() something it > seems to dump > > > as a KEY(). Does it matter if you INDEX ( path, md5, mime ) or > > > INDEX(path), INDEX(md5), INDEX(mime)? They seem to look > different in the > > > db depending how I do it but both seem to run at about the > same speed even > > > over a large db. Either is a huge speed increase over the > original that > > > had only primary keys it seems. > > > Yes they're the same. How you index depends on your queries. Generally, just look at what your doing in your where clauses. If you're looking up rows based just on the path, then index path. If you're looking up rows based on path, md5, and mime at the same time, then index across all three. If you're doing both of the above, then index across all three -- any prefix of an index can be used as an index. > > > Right now in a db with several tables.. some with hundreds of > thousands of > > > rows.. the average query time seems to be about half a second > to a second > > > on a P133. Is that good, bad, or normal? > > > Depends on the query :) On a well-indexed table, a simple select should report no more than a tenth of a second, even on a P133. Of course, that partly depends on how much memory you have... More memory is always a good investment. --------------------------------------------------------------------- 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
