Clarification on FKs and Indexes

[SaranacLake]

@kicken

Hello.  I wanted to get clarification on something we discussed a few months ago regarding FKs and Indexes...

Let's say that I have a typical order system with these tables...

	ORDER
	- id (pk)
	and so on...
	

 

	PRODUCT
	- id (pk)
	and so on...
	

 

	ORDER_DETAILS
	- id (pk)
	- order_id (FK1)(UK)
	- product_id (FK2)(UK)
	and so on...
	

 

A few things I recall you saying to me...

- MySQL requires an index on all FKs.

- Indexes work left-to-right, and so any column that needs to take advantage of an index needs to be in the 1st position when multiple columns are in the index.

- If querying by Orders is more common, then I would want a FK like this: (order_id, product_id)

- I would need another index for product_id.

 

Question:

1.) In order to create an index for the Product_ID FK, and to provide for possibly querying on Product_ID, could my 2nd index be (product_id, order_id)?

 

Having (order_id, product_id) and (product_id, order_id) indexes would help with my two FKs, and all me to query by either Order or Product.

This seems like the best of both worlds, without a lot of overhead from unnecessary indexes.

Thoughts?

 

 

Edited July 29, 2020 by SaranacLake

[requinix]

4 hours ago, SaranacLake said:

- Indexes work left-to-right, and so any column that needs to take advantage of an index needs to be in the 1st position when multiple columns are in the index.

More precisely, the query analyzer will only use an index if the first N columns in it are included in the query.

Given indexes (columnA), (columnB), and (columnA, columnB),

SELECT * FROM table WHERE columnA = 123

could use the (columnA) or (columnA, columnB) indexes.

SELECT * FROM table WHERE columnA = 123 AND columnB = 456

could use the (columnA) or (columnB) or (columnA, columnB) indexes - and the last one is probably best.

Note that (columnA) and (columnA, columnB) are redundant: anything that can use the first one could also use the second one.

 

Quote

- If querying by Orders is more common, then I would want a FK like this: (order_id, product_id)

There's more to it than just about being "common", but that's a decent starting point.

 

Quote

- I would need another index for product_id.

If you wanted to query for products across all orders.

 

Quote

Question:

1.) In order to create an index for the Product_ID FK, and to provide for possibly querying on Product_ID, could my 2nd index be (product_id, order_id)?

Point #1: An index takes up disk space to store the data.
Point #2: Each index slightly reduces performance when making changes to table data because the system has to update its indexes.
Point #3: (order_id, product_id) and (product_id, order_id) are mostly redundant.

Conclusion: I don't think there's enough reason to create both.

Addendum: Actually it kinda depends on the data. But mostly I'm trying to discourage the "moar indexes moar columns" mentality.

[SaranacLake]

@requinix

1 hour ago, requinix said:

More precisely, the query analyzer will only use an index if the first N columns in it are included in the query.

Given indexes (columnA), (columnB), and (columnA, columnB),

SELECT * FROM table WHERE columnA = 123

could use the (columnA) or (columnA, columnB) indexes.

SELECT * FROM table WHERE columnA = 123 AND columnB = 456

could use the (columnA) or (columnB) or (columnA, columnB) indexes - and the last one is probably best.

Okay, good to know.

 

 

1 hour ago, requinix said:

There's more to it than just about being "common", but that's a decent starting point.

Okay.

 

1 hour ago, requinix said:

Point #1: An index takes up disk space to store the data.
Point #2: Each index slightly reduces performance when making changes to table data because the system has to update its indexes.
Point #3: (order_id, product_id) and (product_id, order_id) are mostly redundant.

Conclusion: I don't think there's enough reason to create both.

Addendum: Actually it kinda depends on the data. But mostly I'm trying to discourage the "moar indexes moar columns" mentality.

That being said, and knowing that I will be querying on Orders and Order Details, and knowing that I need a FK on Order_ID and Product_ID, then it sounds like you are suggesting that I create one index on {Order_ID, Product_ID) and then my second index simply on (Product_ID), correct?

...

[requinix]

1 hour ago, SaranacLake said:

it sounds like you are suggesting that I create one index on {Order_ID, Product_ID) and then my second index simply on (Product_ID), correct?

Pretty much. The columns represent discrete identifiers that have no intrinsic numerical properties, meaning there's no reason you should ever do anything based on an order or product ID range, therefore all your queries will be straight equality - whether the row is or is not for a particular order and/or product. As such there are no benefits to having dual indexes on both columns.

[SaranacLake]

@requinix 

 

Okay, thanks!