I have an invitations table in my database, which has got a from and to columns, which are both foreign keys for the userId column in my users table.
我的数据库中有一个invite表，它有一个from和to列，它们都是我的users表中的userId列的外键。
For example, if User # 1 invited User # 2 to do something, then in the invitations table, from will be 1, and to will be 2.
例如，如果用户# 1邀请用户# 2做某事，那么在invite表中，from将是1,to将是2。
I will use the to column in where statements, such as:
我将使用where语句中的to列，例如:
SELECT * FROM invitations WHERE `to` = $currentUserId
But the from column will never be used in WHERE statements.
但是from列永远不会在WHERE语句中使用。
From what I've read, you should only add an index on the columns which will be used in WHERE statements, however I've also read that you should always create Foreign keys.
从我所读到的内容中，您应该只在语句中使用的列上添加一个索引，但是我也读过，您应该总是创建外键。
Does it make sense for me to create an index on from just to add a foreign key?
对我来说，仅仅添加一个外键就创建一个索引有意义吗?
2 个解决方案
#1
2
In the case of foreign keys, you should add one depending on what you do with users.
对于外键，应该根据用户的使用情况添加一个。
Internally, your fkeys will fire an action on any insert/update/delete of invitations, but also on any update/delete of users(id).
在内部，您的fkeys将在任何插入/更新/删除邀请时触发一个操作，但也会在任何更新/删除用户(id)上进行操作。
The first of these will use the index on users(id) to check if the user exists.
第一个将使用用户索引(id)检查用户是否存在。
The other will essentially run a query that goes: do whatever you defined to do on update/delete from invitations where to/from = :id.
另一个基本上运行一个查询:做任何你定义的更新/删除从邀请到/从=:id。
So, if you change the id of users (very unlikely) you can make use of an index. And if you occasionally delete users (only slightly more likely), you can make use of the index.
因此，如果您更改用户的id(非常不可能)，您可以使用索引。如果您偶尔删除用户(只是稍微有点可能)，您可以使用该索引。
And as point out in the other answer, indexes are also useful for queries with an order by/limit clause.
正如在另一个答案中指出的，索引对于带有order by/limit子句的查询也很有用。
#2
1
you should only add an index on the columns which will be used in WHERE statements
您应该只在WHERE语句中使用的列上添加一个索引
It is not so cut and dry. It is a good rule of thumb, but there is more to it than just this.
它不是那么干瘪。这是一个很好的经验法则，但它不仅仅是这个。
You should also index columns that you order by often and always, always test before and after a change to see if you have improved performance.
您还应该按经常和总是的顺序对列进行索引，在更改前后始终进行测试，以查看您是否提高了性能。
Foreign keys in general will not benefit from having an index defined on them, so long as they are not part of where/order by clauses. If they are, you should index them. (again, rules of thumb).
一般来说，外键不会受益于在它们上定义索引，只要它们不是where/order by子句的一部分。如果它们是，你应该将它们编入索引。(经验法则)。
This looks like a good resource for optimizing MySQL - look towards the bottom where it explains about indexing.
这看起来像是优化MySQL的一个很好的资源——看下面，它解释了索引。
#1
2
In the case of foreign keys, you should add one depending on what you do with users.
对于外键，应该根据用户的使用情况添加一个。
Internally, your fkeys will fire an action on any insert/update/delete of invitations, but also on any update/delete of users(id).
在内部，您的fkeys将在任何插入/更新/删除邀请时触发一个操作，但也会在任何更新/删除用户(id)上进行操作。
The first of these will use the index on users(id) to check if the user exists.
第一个将使用用户索引(id)检查用户是否存在。
The other will essentially run a query that goes: do whatever you defined to do on update/delete from invitations where to/from = :id.
另一个基本上运行一个查询:做任何你定义的更新/删除从邀请到/从=:id。
So, if you change the id of users (very unlikely) you can make use of an index. And if you occasionally delete users (only slightly more likely), you can make use of the index.
因此，如果您更改用户的id(非常不可能)，您可以使用索引。如果您偶尔删除用户(只是稍微有点可能)，您可以使用该索引。
And as point out in the other answer, indexes are also useful for queries with an order by/limit clause.
正如在另一个答案中指出的，索引对于带有order by/limit子句的查询也很有用。
#2
1
you should only add an index on the columns which will be used in WHERE statements
您应该只在WHERE语句中使用的列上添加一个索引
It is not so cut and dry. It is a good rule of thumb, but there is more to it than just this.
它不是那么干瘪。这是一个很好的经验法则，但它不仅仅是这个。
You should also index columns that you order by often and always, always test before and after a change to see if you have improved performance.
您还应该按经常和总是的顺序对列进行索引，在更改前后始终进行测试，以查看您是否提高了性能。
Foreign keys in general will not benefit from having an index defined on them, so long as they are not part of where/order by clauses. If they are, you should index them. (again, rules of thumb).
一般来说，外键不会受益于在它们上定义索引，只要它们不是where/order by子句的一部分。如果它们是，你应该将它们编入索引。(经验法则)。
This looks like a good resource for optimizing MySQL - look towards the bottom where it explains about indexing.
这看起来像是优化MySQL的一个很好的资源——看下面，它解释了索引。