You might benefit from drawing your layout as a picture, if you haven't already. Use arrows to connect the fields in each table with the fields in other tables that they will "hook" to. That will give you an idea of which fields to index and JOIN on. You JOIN on those fields, and you index the ones that have many different values.
Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Michael Higgins [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 02, 2007 3:20 PM > To: 'Dan Buettner' > Cc: mysql@lists.mysql.com > Subject: RE: basic architecture review? > > > -----Original Message----- > > From: Dan Buettner [mailto:[EMAIL PROTECTED] > > > > Michael, this looks pretty decent overall. > > > > I'm a big fan of fully descriptive table and column names, > > [8<] (All good suggestions, thanks.) > > > One performance suggestion: add an index on each table for > > the claim_id column. This will greatly speed retrieval of > > material related to a given claim/overview. ALTER TABLE x > > ADD INDEX claim_id_idx (claim_id) > > Okay, this was exactly the kind of question I had. So, if I > do that, then > mysql will just access that INDEX information internally? IOW, I don't > actually query on that field, or ever have to think about it > again, right? > > > > > When you say "query the lot", what do you mean? Get all > > related "stuff" in a single SQL statement? Possible, but > > maybe a bit messy, and not as easy to maintain as a handful > > of routines that each get documents, emails, pictures. As > > you add more tables holding related material the SQL would > > become unwieldy and you'd likely break it down later anyway. > > > > Yeah, I get that... but what I'm looking for is to select > (all non-blob > fields) from (all the tables) where claim_id = xxxxx ..... > [what do I do > here? some kind of a 'join'?] > > This way, I'd be able to get access to each record associated > with that > claim_id from one, say, webpage. > > Like, having retrieved an image_id from the monolithic query, > I could then > retrieve the associated image blob with another query. > > Anyway, I'm sure this will all become clearer to me eventually... ;-) > > Thanks a bunch, > > Michael Higgins > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
