[Cialug] mysql database design

[Matthew Nuzum]

On 8/9/07, Dave J. Hala Jr. <dave at 58ghz.net> wrote:
> I'm working on a database design for storing and retrieving demographic
> information. The server is Mysql 4.x
>
> Basically, I have a "root" table with a primary key and ten tables with
> records (10,000 of them) that are related to the root table via the
> primary key.  Inserting the data is at the moment a non-issue. However,
> retrieval is. Generally speaking I do not do joins across more then 3
> tables.  In this instance it could be helpful to do a join across more
> than 3.
>
> My concern is performance. Has anyone been in this situation before? How
> did your database design get around this issue?

I've got some queries that incorporate way more than three tables in a
join. The trick is to have proper indexes.

My rule of thumb is to just write the query and then do an explain on
it to see how it does. Then, if I have problems with performance,
explain tells me where the culprit is and I can be certain to fix just
the problem I'm having. (often by adding the proper index)

You can just index everything if you want, but then you'll have
problems with insert speed. :-)

http://dev.mysql.com/doc/refman/5.0/en/explain.html

-- 
Matthew Nuzum
newz2000 on freenode