Search code examples
mysqlmany-to-many

MySQL many-to-many optimal indices

I have a following MySQL code for table:

CREATE TABLE `tag_to_photo` (
  `tag_id` INT UNSIGNED NOT NULL,
  `photo_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`tag_id`, `photo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

How to optimally define indices if I am going to search for both: all tags pinned to photo and all photos pinned to tag? I assume primary key can be combined. But should I define additional index for tag_id or photo_id or maybe for both?

Solution

  • Multiple column indexes are called Composite Keys, and you do not need to add separate indexes for the said columns because the Primary Composite Key will be enough, assuming most of your queries are similar to this:

    SELECT  ...
    FROM tag_to_photo
    WHERE tag_id = ...
        AND photo_id = ...

    Check the link for Composite Keys to learn more on how you can take advantage of its performance.

    This SO Question will also provide some under the hood about composite keys, depending on your database configuration (InnoDB/MyISAM).

    There could be instances that the query will get slow for INSERT/UPDATE when the table get huge. That time we can analyze the execution plan on what we can do and how to implement them to optimize your queries.