[Cialug] mysql database design

[Morris Dovey]

Dave J. Hala Jr. wrote:
| The primary key is an integer.
|
|
| On Thu, 2007-08-09 at 09:33 -0500, Morris Dovey wrote:
|| Dave J. Hala Jr. 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?

In a couple of the systems I've worked on we implemented trie indexes
for _extremely_ fast retrieval. In one case the trie was used in
parallel with the indexing provided in a canned db package, and in the
others it was the only index used.

If you're not familiar with the trie, you can probably find good info
via Google. It provides very fast retrieval, but only so-so insertion
speed.

Morris Dovey
DeSoto Solar
DeSoto, Iowa USA
http://www.iedu.com/DeSoto/