-1

My question is regarding database structuring for a table that links 2 other tables for storing the relationship.

for example, I have 3 tables, users, locations, and users_locations.

users and locations table both have an id column.

users_locations table has the user_id and location_id from the other 2 tables.

how do you define your indexes/constraints on these tables to efficiently answer questions such as what locations does this user have or what users belong to this location?

eg.

select user_id from users_locations where location_id = 5;

or

select location_id from users_locations where user_id = 5;

currently, I do not have a foreign key constraint set, which I assume I should add, but does that automatically speed up the queries or create an index?

I don't think I can create an index on each column since there will be duplicates eg. multiple user_id entries for each location, and vice versa.

Will adding a composite key like PRIMARY_KEY (user_id, location_id) speed up queries when most queries only have half of the key?

Is there any reason to just set an AUTO INCREMENT PRIMARY_KEY field on this table when you will never query by that id?

Do I really even need to set a PRIMARY KEY?

Alan Mayer
  • 79
  • 6
  • Generally - 2 indices, `(field1, field2)` and `(field2)`, when disk space usage is minor. Else one composite index. Anycase in the composite index the field listed first must be more variable (have more distinct values than another field). – Akina Feb 27 '20 at 16:20
  • https://stackoverflow.com/questions/571309/how-to-properly-index-a-linking-table-for-many-to-many-connection-in-mysql this answers this question quite well – Alan Mayer Feb 28 '20 at 14:17

2 Answers2

0

Basically, for any table, decision to create an index or not create an index, totally depends on your use cases which you support. Indexes must always be on the per use basis and not on nice to have.

For your particular queries that you have mentioned, separate indexes on both the columns are good enough, that is query doesn't need to go to your rows to fetch the information.

Creating foreign key on a table column automatically creates an index so you need not create indexes yourself if you decide to set up foreign keys.

If you keep an auto increment key as primary key, you will still have to make user_id and location id combination as unique otherwise you will bloat your table with duplicates.So keeping a separate auto increment key doesn't make sense in your use case. However if you want to keep track of each visit to a location and save user experience each time then auto increment primary key will be a required thing.

However I would like to point it out that creating indexes does not guarantee that your queries will use them unless specified explicitly. For a single query there can be many execution plans and most efficient may not use an index.

Vishal
  • 166
  • 6
  • I'm not sure I understand, which indexes are good enough? Adding the foreign key constraints? Adding the composite key primary key? or adding an auto increment primary key? I currently have no index set up – Alan Mayer Feb 27 '20 at 16:17
  • I meant if you set up foreign keys, the automatically added index will be good enough for the queries that you have mentioned. – Vishal Feb 27 '20 at 16:22
0

The optimal indexes for a many-to-many mapping table:

PRIMARY KEY (aid, bid),
INDEX(bid, aid)

More discussion and more tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

(Comments on specific points in the Question)

  • FOREIGN KEYs implicitly create indexes, unless an explicit index has already been provided.
  • Composite indexes are better for many-to-many tables.
  • A FOREIGN KEY involves an integrity check, so it is inherently slower than simply having the index. (And the integrity check for this kind of table is of dubious value.)
  • There is no need for an AUTO_INCREMENT on a many:many table. However, ...
  • It is important to have a PRIMARY KEY on every table. The pair of columns is fine as a "natural" PRIMARY KEY.
  • A WHERE clause would like to use the first column(s) of some index; don't worry that it is not using all the columns.
  • In EXPLAIN you sometimes see "Using index". This means that a "covering index" was used. That means that all the columns used in the SELECT were found in that one index -- without having to reach into the data to get more columns. This is a performance boost. And necessitates two two-column indexes (on is the PK, one is a plain INDEX.)
  • With InnoDB, any 'secondary' index (INDEX or UNIQUE) implicitly includes the columns of the PK. So, given PRIMARY KEY(a,b), INDEX(b), that secondary index is effectively INDEX(b,a). I prefer to spell out the two columns to point out the to reader that I deliberately wanted those two columns in that order.
  • Hopefully, the above link will answer any further questions.
Rick James
  • 135,179
  • 13
  • 127
  • 222