where to add an index-CodePudding

I have always struggled with knowing where to add an index. I would love for someone to explain to me the way they approach adding indexes and explain to me like you would a 10 year old child.

Say i had a table which had a good amount of columes lets say

user
age
height
related_user
foo2
banned
location
created on

now say i had a complex query which uses many forms of these to build up the results so sometimes it look for age > 10 and foo2 = true or something it look for related_user IS NULL or banned = false

it is using everything within the table to built up many things.. in an situation like this where do i start in looking to add a index and how do i know its going to make a difference.

I know this is not an exact problem but i think a lot of people would benefit from this question.

Thanks guys!

CodePudding user response：

I wrote some recommendations to you for about indexes:

Add an index to fields that are frequently used in conditions or in order by.

If two fields in same time uses in conditions recommended creating one index for these fields, for example:

where (a = 5) and (b > 20) =>> CREATE INDEX indexname ON your_table (a, b);

For conditions which always using in your queries recommended using a partial index. For example:

where (a > 100) and active = true =>> CREATE INDEX indexname ON your_table (a) where active = true;

where (a > 100) and (b<500) and active = true =>> CREATE INDEX indexname ON your_table (a, b) where active = true;

Always try to use int type indexes instead of string types.

Use index types correctly, for example: for INT, STRING, DATE types - use BTREE index, for ARRAY, JSON, JSONB types use GIN, GIST indexes