Question 20474 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL indexes on id?

Should I put INDEX on staff_id & client_id

CREATE TABLE conversation (
    id INT(7) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    staff_id INT(7) UNSIGNED,
    client_id INT(7) UNSIGNED,
    summary TEXT,
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE,
    INDEX( staff_id, client_id )
);

What about a many-to-many relationship table? it there any point in putting index there?

CREATE TABLE client_contact (
    id INT(7) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT(7) UNSIGNED,
    contact_id INT(7) UNSIGNED,
    FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE,
    FOREIGN KEY (contact_id) REFERENCES contact(contact_id) ON DELETE CASCADE
    UNIQUE ( client_id , contact_id )
);
Tags:

2

Answers


  1. 0

    As both of these tables are likely to be queried in both directions, you should have both versions of the composite indices. For the m-2-m table you should also drop the surrogate primary key. You have specified INT(7) everywhere, which probably means you want MEDIUMINT (max unsigned 16,777,215), and integer display width is deprecated. I have also added NOT NULL to all the FKs, obviously you can change based on your needs:

    CREATE TABLE conversation (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    staff_id INT UNSIGNED NOT NULL,
    client_id INT UNSIGNED NOT NULL,
    summary TEXT,
    INDEX ( staff_id, client_id ),
    INDEX ( client_id, staff_id ),
    FOREIGN KEY ( staff_id ) REFERENCES staff ( staff_id ) ON DELETE CASCADE,
    FOREIGN KEY ( client_id ) REFERENCES client ( client_id ) ON DELETE CASCADE
);

CREATE TABLE client_contact (
    client_id INT UNSIGNED NOT NULL,
    contact_id INT UNSIGNED NOT NULL,
    PRIMARY KEY ( client_id , contact_id ),
    INDEX ( contact_id, client_id ),
    FOREIGN KEY ( client_id ) REFERENCES client ( client_id ) ON DELETE CASCADE,
    FOREIGN KEY ( contact_id ) REFERENCES contact ( contact_id ) ON DELETE CASCADE
);
    Login or Signup to reply.
  2. 0

    "Should I put INDEX on…" — The answer always comes from the queries that use the table. It is not possible to give you the correct/complete answer from just the CREATE TABLE.

    However, since both of your tables smell like "many-to-many mapping tables", I will give you this answer: Many-to-many

    @user1191247 got client_contact correct. But I can’t tell about conversation. If there can be only one staff:client pair, then I would argue against having id.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search