1

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?

no one special
  • 1,608
  • 13
  • 32

1 Answers1

1

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.

KaeL
  • 3,639
  • 2
  • 28
  • 56
  • I will have the following quaries only: SELECT tag_id FROM tag_to_photo WHERE photo_id = ...; SELECT photo_id FROM tag_to_photo WHERE tag_id = ... OR/AND tag_id = ... AND/OR ...; – no one special Mar 27 '18 at 11:07
  • I notice that tag_id is (mostly) present in your query. Just take note that leftmost index rule of composite keys and you’ll be fine. – KaeL Mar 27 '18 at 14:50