Mysql – Have foreign key constraint and index key for the same column(s)

database-designforeign keyindexMySQL

I am creating a composite index key for 2 columns and then adding foreign keys to its appropriate table. Do I need to create a multiple index key reference and then create separate foreign keys? My example of how I have achieved it is below:

CREATE TABLE IF NOT EXISTS customers_appointments (
appointment_id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
merchant_id int(11) NOT NULL,
supplier_id int(11) NOT NULL,
appointment_date datetime NOT NULL,
appointment_description mediumtext NOT NULL,
appointment_confirmed tinyint(1) NOT NULL DEFAULT '0',
appointment_confirmed_date datetime NOT NULL,
date_created datetime NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

ALTER TABLE customers_appointments 
 ADD KEY appointments_index (merchant_id,supplier_id), 
 ADD KEY fk_merchant_appointment (merchant_id), 
 ADD KEY fk_supplier_appointment (supplier_id);

ALTER TABLE customers_appointments
  ADD CONSTRAINT fk_merchant_appointment 
    FOREIGN KEY (merchant_id) REFERENCES merchants (merchant_id) 
    ON DELETE CASCADE,
  ADD CONSTRAINT fk_supplier_appointment 
    FOREIGN KEY (supplier_id) REFERENCES suppliers (supplier_id) 
    ON DELETE CASCADE;

This is working for me but I just need to know from an expert that in terms of performance and how it is designed is correct?

Best Answer

Constraints and Indexes serve two different purposes in the database.

Constraints prevent data that violates the constraints from being stored in the database. This protects future users of the data, but it limits flexibility and adds overhead. For foreign key constraints such as the ones you have presented, the overhead is usually small, compared with the benefit of avoiding orphan references.

Indexes provide a map between key values and retrieval pointers. SQL users never deal directly with retrieval pointers in SQL. (Well, there are some dialects of SQL that let you do that, but it's generally a bad idea anyway.) When the optimizer is evaluating strategies for retrieval, and estimating the cost, the presence or absence of an index may make the optimizer choose one strategy over another. Sometimes the speed-up is dramatic. Indexes cost a little space to store, and a little time to update when the data changes. Some indexes are worth the cost, others are not.

In your case, I would play around with leaving out the index you've called fk_merchant_appointment. It may be the case that MySQL will use the apointments_index instead, and get most of the benefits with less cost. I can't say for sure.

Related Question