0

If I have a relationship between two tables like this:

CREATE TABLE parent (id INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE child (id INT NOT NULL, parent_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

And I want to find all of the rows in the child table that are referencing one row in the parent table, is it faster to:

  1. Simply query the child table and return all that match the id of the parent row
  2. Store a comma separated list of ids in a column in the parent table
  3. None of the above
Jonathan Eckman
  • 2,071
  • 3
  • 23
  • 49
  • do you also want to find deeper level of children? – John Woo Jan 02 '13 at 00:39
  • 1
    [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Jan 02 '13 at 00:41
  • @JW yes the child table will have children that may need to reference the parent (now grandparent) table – Jonathan Eckman Jan 02 '13 at 00:45
  • Adding a `FOREIGN KEY` [implicitly adds an index](http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html), and returning all rows that matches an index is generally fast enough. You'll probably need to run a benchmark to see if (1) or (2) is faster, but, as Bill Karwin pointed out, (2) is a bad idea. – Bernhard Barker Jan 02 '13 at 01:14

1 Answers1

0

A foreign key is naturally indexed, so the best way to go is to execute this query:

SELECT * FROM child WHERE parent_id=X;
-- where X is the parent's ID
zfou
  • 891
  • 1
  • 10
  • 33