MySQL table structure question.

Question

I was wondering how should my categories mysql table look like and which INDEX or KEY is correct or are all four correct?

INDEX (parent_id)

INDEX parent (parent_id)

INDEX parent_id (parent_id)

KEY parent_id (parent_id)

Here is my MySQL code.

CREATE TABLE categories ( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
parent_id INT UNSIGNED NOT NULL DEFAULT 0, 
category VARCHAR(255) NOT NULL, 
url VARCHAR(255) NOT NULL,
depth INT NOT NULL DEFAULT 0, 
PRIMARY KEY (id), 
INDEX parent (parent_id),
UNIQUE KEY (parent_id, url)
);

Answer 1

First of all you need to eliminate one of the duplicate indexes (parent or parent_id). Then, most probably, you need your KEY to be id and not parent_id.

Lastly, the depth field is redundant, since the depth can be calculated from the category's hierarchy. Sometimes though, this duplication of data is needed for performance reasons. If you know why exactly you need the depth field then you can keep it.