Best practices for setting indexes

0
Dear all, I currently have a table with 50.000 entries and I would like to improve the query times in this table. My application has allot of xpath retrieves that involve a enum value with 10 distict different values that are all comparably frequent in my database. However, these retrieves often include 'AND / OR' statements on other attributes or are combined with retrieves over associations to other objects. Will adding an index on this field improve these retrieves or will it have little effect compared to the extra cost needed for saving new objects? Medix documentation states: Indexes are ordered. This means that when creating an index on two or more attributes, it is important to consider the order of the attributes. When performing a search or query on multiple attributes, these attributes should be in the same order as those in the index to take advantage of the improved performance. By extension, when the retrieval is constrained by only one attribute, the improved performance is only achieved if this is the first attribute in the index. Since the associations are on Mendix guid, I interpret this as that adding this index will have no benefit. Additionally it is not clear if performance is improved for statements with 'AND/OR' attributes that are not added to the index. For me getting a understanding on databases the following link helps explain.
asked
3 answers
1

Generally, the DB engine will also optimize when AND/OR is involved. The index should make a difference.

Adding an index does add to the cost of inserting a record (create an object). However, having a few well placed indices should be no issue. Several dozens of indices will probably cause performance issues. There is a grey area inbetween.

Use your commen sense in determining your index strategy.

Also, try to avoid 'contains' search elements, because an index will not help there. Searching using starts with, greater or less than can be optimized using an index. Don't overdo it by setting an index for every search field.

Some considerations:

  • The DB engine will probably ignore your index when the table has a small number of records
  • Use as few indices as possible when a large entity is very frequenty updated.
  • Use plenty indices on base tables or history tables that get queried often but don't change frequently.

Test your scenario using a production (-like) copy in a test environment. Make sure that the test environment has sufficient resources. Quite often you can run some tests without restoring the file document contents.

answered
1

If you really want to understand how the database is using your indexes, it's best to have a local database and look at the explain plan of the queries executed. You can find some info here: http://mxblog.eu/link/post/sql-is-about-what-data-you-want-not-how-to-get-it

answered
0

Hi Ralph, I didn't know about the order of the index, so thanks for pointing that one out. For me a rule of thumb is that an index performs best when it contains unique values like the persons last name or a timestamp. Having that said, I'm not sure if it's better to create an index on the enumerator, but your test should verify this.

answered