3

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?

1 Answer 1

3

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.

11
  • Hi Walter Mitty thanks for the prompt reply. Would this also refer to fk_supplier_appointment index too? So really the only index would be appointments_index? I didn't want to create redundant data for the future hence why I have added FK constraints. I have read on some posts when researching that MySQL usually uses the index key(s) rather than FK constraints when performing certain actions?
    – Cam
    Commented Jun 6, 2015 at 13:46
  • You should add that some DBMS require indexes to enforce (with less cost) some constraints so the two concepts (indexes, constraints) are related due to implementation. Postgres for example, for primary key and unique constraints, MySQL for primary key, unique and foreign key constraints, Commented Jun 6, 2015 at 14:06
  • In fact, mysql implementation is a bit fuzzy and does not separate well between indexes and constraints, Anyway, you should have, as Walter suggests, at least one index where supplier_id is the 1st column (of the index) and another where merchant_id is the 1st column. Commented Jun 6, 2015 at 14:09
  • Hi ypercube thanks for the prompt response. Could you demonstrate how the above table should be created then please? :) So that I am in peace of mind on how it should be done. Thanks Cam
    – Cam
    Commented Jun 6, 2015 at 14:20
  • ypercube's comment is true and useful. Commented Jun 6, 2015 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.