number of indexes for several retrieve

0
Hi,  For an entity that is being retrieved a lot in our application. We want to implement indexes.  sometimes we retrieve the objects with name and city. sometimes with only name. sometimes with only city. Should we implement 3 indexes for those 3 cases? (name and city, name, city) Or only 1 with name and city?  Or 2 with name, and the other one with city?   Hope this is clear, Thanks!
asked
2 answers
1

Hey Yehoshua,

this stackoverflow post has great explanation of your case.

https://stackoverflow.com/questions/4377525/using-more-than-one-index-per-table-is-dangerous

I have table that has around 20 attributes (around 400,000 objects) and almost every attributes is indexed I didn't have any issue on updating that table only issue was with deleting objects from table and that wasn't related to index it was because of cascaded delete option.

Best regards, Slavko

answered
1

As far as I have an understanding of how indexes work (my experience is in another developing language so other type of database but guess the bases will be the same) .... are 2 indexes enough in your case.

So 1 on Name and 1 on City (but only usefull when you have a lot of data in the entity and the search is performed frequently and needs to be quick)

I have no idea if your type of database automatically combines different indexes on the same entity (so that would make it even more performant) but again 2 indexes should be enough

answered