* Chris W
> I have two tables with a one to many relationship: boys and their toys.
> I want to know if there are any advantages or disadvantages for each
> of the following two ways to create the tables. I am mainly interested
> in performance. Either way the insert and select queries aren't any
> more difficult than the other way.
>
> CREATE TABLE boy (
> BoyID INT NOT NULL AUTO_INCREMENT,
> name CHAR(30) NOT NULL,
> PRIMARY KEY (BoyID)
> );
>
> CREATE TABLE toy (
> BoyID INT NOT NULL,
> ToyID INT NOT NULL AUTO_INCREMENT,
> name CHAR(30) NOT NULL,
> PRIMARY KEY (BoyID,ToyID)
> );
This design is without a key for 'toys', the table could have been named
'boystoys' or similar to make that more clear. But that may be okay for you
if you are sure this design covers your needs. My immediate instinct says I
would need three tables, one for boys, one for toys and one for the
ownerships ("boystoys"). That way multiple boys could own the same toy
(sharing a toy, or if that is not relevant, sharing a "class of toys", like
'bicycle'), or toys could be sold/bought/traded, such that the owner of the
toy changes over time. But this will of course depend on the application.
The ToyID will start from 1 for each boy, is this something you need? If
yes, this is the way to do it. If no, I would go for the second design.
> OR
>
>
> #no change here
> CREATE TABLE boy (
> BoyID INT NOT NULL AUTO_INCREMENT,
> name CHAR(30) NOT NULL,
> PRIMARY KEY (BoyID)
> );
>
> #slight change here
> CREATE TABLE toy (
> ToyID INT NOT NULL AUTO_INCREMENT,
> BoyID INT NOT NULL,
> name CHAR(30) NOT NULL,
> PRIMARY KEY (ToyID)
> );
This design has a "real" toy table, the design is easy to extend, for
instance with a 'toytype' or 'boystoys' table, but it lacks an index on the
BoyID column. This will be slow when you select toys for a specific boy. It
can be fixed simply by adding an index on BoyID. And because MySQL can
optimize queries where all relevant info is in the index, one can often
benefit from combining keys in a single index:
ALTER TABLE toy ADD UNIQUE (BoyID,ToyID)
If you query like this:
SELECT boy.name
FROM boy, toy
WHERE
toy.BoyID = boy.BoyID AND
toy.ToyID < 100
This would give you the name of boys who own the 100 'oldest' toys (unless
you have deleted some toys...), and it would be able to use the combined
index, which means it does not need to physically read the toy table at all.
In contrast, this statment must also read the physical toy row to check the
name of the toy:
SELECT boy.name
FROM boy, toy
WHERE
toy.BoyID = boy.BoyID AND
toy.name = "bicycle"
This query would have benefited from an index on toy.name:
ALTER TABLE toy ADD INDEX (name)
or
ALTER TABLE toy ADD UNIQUE (BoyID,name)
This final index could be used instead of the (BoyID,ToyID) index, if the
"ToyID < 100" type of criteria is not needed, but the boy.name column is.
Note that you would probably also need an index with 'name' as the first or
the only column, at least if there are many toys and you sometimes need to
search for a toy based on the name of the toy.
The 'name' column is a CHAR(30) (why not a VARCHAR?), you can index on a
substring, for instance:
ALTER TABLE toy ADD INDEX (name(10))
This would make an index on only the first ten characters of the toy name.
This can be faster in some cases, but I don't know which...
You should probably also have an index on boy.name.
If you need more specific information, please include some details about
your application: what kind of queries, how many simultaneous users, how
much data, what kind of hardware, what OS...
HTH,
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
