In terms of index efficiency, the joint index is definitely more efficient. In many cases, if you use multiple fields to query, you should consider using the joint index. But things are not completely absolute, and the overhead of indexing must also be taken into consideration. Take your conditions as an example. Assuming thatkeycan uniquely determine a record, is it unnecessary to addparentId?key能够唯一确定一条记录，parentId是不是就没有必要加上了呢？ 退一步，即使key不能唯一确定一条，如果它能够把结果集确定在一定的小范围内，比如5条记录，10条记录，那parentIdTake a step back, even ifkeycannot uniquely determine one, if it can determine the result set within a certain small range, such as 5 records or 10 records, thenparentIdThis condition is nothing more than scanning these 10 records again to find the appropriate record. Compared with the writing, storage, and memory overhead caused by adding it to the index, I may choose not to put it into the joint index at all. The more records a condition can filter out, the better its "selectivity" is. Generally speaking, when we build an index, we should put the conditions with better selectivity at the front and the conditions with poor selectivity at the back. If it's bad enough, don't let it in. This is actually a balance of time and space. Conditions put into the index save time (including CPU time, query time) and space (including storage space, memory space); conditions not put into the index cost time and save space. Most of the time we expect the former. When to choose the latter depends on your own assessment of the actual situation.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
In terms of index efficiency, the joint index is definitely more efficient. In many cases, if you use multiple fields to query, you should consider using the joint index.
But things are not completely absolute, and the overhead of indexing must also be taken into consideration.
Take your conditions as an example. Assuming that
keycan uniquely determine a record, is it unnecessary to addparentId?key能够唯一确定一条记录，parentId是不是就没有必要加上了呢？退一步，即使
key不能唯一确定一条，如果它能够把结果集确定在一定的小范围内，比如5条记录，10条记录，那parentIdTake a step back, even ifkeycannot uniquely determine one, if it can determine the result set within a certain small range, such as 5 records or 10 records, thenparentIdThis condition is nothing more than scanning these 10 records again to find the appropriate record. Compared with the writing, storage, and memory overhead caused by adding it to the index, I may choose not to put it into the joint index at all.The more records a condition can filter out, the better its "selectivity" is. Generally speaking, when we build an index, we should put the conditions with better selectivity at the front and the conditions with poor selectivity at the back. If it's bad enough, don't let it in.
This is actually a balance of time and space. Conditions put into the index save time (including CPU time, query time) and space (including storage space, memory space); conditions not put into the index cost time and save space. Most of the time we expect the former. When to choose the latter depends on your own assessment of the actual situation.