List View Threshold in SharePoint

It's been a few years I have started working with SharePoint and lists, libraries, user groups and etc.. are now seems like much interesting from the internal way they are handled. Never faced a situation where 5000+ list items are involved in the days of training and now it seems like the best times where I get to know the performance management is important and other considerations are much valuable when it comes to the development.

SharePoint has this interesting feature that actually saves the database servers being loaded with heavy queries on list items and it is called list view threshold. By default it is set to 5000 for normal users and 20000 for users with administrative privileges. This can be set to any value at anytime and also disable and allow any load to be put on the db server, which is equivalent to breaking the fence around the house and asking people to come in thousands.

By the definition, it specifies the maximum number of items that a database operation can involve at one time. So when list view threshold is 5000, its 5000 items. But this does not mean it will display list items when there are less than or equal 5000 list items.

I got an opportunity to tryout this thing at office and my observations are as follows.

List View Threshold	List Items	Filter By	Displays List Items	OOTB Filtering Works
5,000	1,000	none	Yes	Yes
5,000	10,000	none	Yes	No
5,000	10,000	ID	Yes	No
5,000	10,000	Lookup column	No	No
10,000	10,000	Lookup column	Yes	Yes

According to these observations it shows that when you are applying filtering with any lookup column, then the list item count should be the minimal requirement for list views to work. So it seems SharePoint from the inside joins the tables associated with the lookup and then perform the filtering.