Search code examples
mysqldatabase-designrelational-databaseone-to-manyrdbms

RDBMS store list of children in father table

enter image description here

If I want to find all the children of "John", i need to run SELECT * FROM Child where Father_Name = "John" which searches through the entire Child table.

Is there a way to design it such that the Father table will contain the list of children so that i can get all children of "John" by simply searching for the "John" row in the Father table?

Solution

  • You need an index on Father_Name in the Child table.

    Read up on the CREATE INDEX statement in MySQL. Once that index is in place, MySQL will use it when you execute the query you presented. This will be dramatically faster than scanning the entire table. The larger the table, the more dramatic the improvement.

    You didn't ask about this, but I'll toss it in anyway. Using a VARCHAR(45) field as a primary key is generally bad as far as performance is concerned. Most database designs add an identifying number to each entity for use as a primary key, and as a foreign key in other tables or other rows in the same table. Integers produce smaller and faster indexes than character strings.