Difference between revisions of "IBM CMOD Composite Indexes"

IBM CMOD Composite Indexes (view source)

Revision as of 22:30, 20 November 2020

956 bytes added , 22:30, 20 November 2020

m

Added more analogies about CMOD index fields, and caveats for choosing which fields should be CMOD indexes.

Jderrick

Bureaucrats, Administrators

1,126

edits

@@ Line 8: / Line 8: @@
 Database indexes make access to data faster by storing a value with the location of the data in the index -- similar to how the index at the back of a reference book works.  The advantage is that if the data being indexed is mostly unique ("high cardinality") the indexes can be used to make searches very fast.  In the OnDemand Application Group configuration window, in the Field Information tab, fields that are set to 'Index' should also have the 'Required' checkbox selected in the Folder configuration, Field Information Tab.  This forces end users to enter a value into the field in order to perform a search - reducing the potential for end users to submit queries that will perform poorly (consuming a disproportionate amount of server I/O bandwidth, RAM, and CPU), as well as reducing the possibility that one of these 'rogue queries' will impact the performance of other user's searches or retrievals.
-For those who like analogies:  Using 'Filter' Field Type is like trying to find a word by searching every page in a very long book.  Using 'Index' is like using the index at the back of a book, telling you where to find each occurrence of the word by page number.  In fact, this naming convention is used in databases -- indexes point to database pages (like pages in a book), which contain records ("words").
+For those who like analogies:  Using 'Filter' Field Type is like trying to find a word by searching every page in a very long book.  Using 'Index' is like using the index at the back of a book, telling you where to find each occurrence of the word by page number.  In fact, this naming convention is used in databases -- indexes point to database pages (like pages in a book), which contain records ("words").  For this reason, you want indexes on fields that are the '''most unique''' like 'customer number' which should uniquely identify a customer account, but not 'zip code', which could encompass tens of thousands of customers in large cities.  To continue the book analogy, the index in the back of your book doesn't contain a list of the pages that the words "and", "the", "a" or "to" -- because those words are likely to appear on every page of the book, and are not just useless, but also consume a huge amount of space while being useless.
 Composite Indexes are database indexes that are created by adding the values of two or more fields together.  If the fields selected for the composite indexes are mostly unique (high specificity or cardinality) the index may be very large, but it should be able to identify a single record -- and be extraordinarily efficient in the process.  In order to be effective at reducing the duration of queries, ALL of the fields that appear in the composite index must also be set to be 'Required' fields in the Folder configuration.
 [[File:RequiringIndexedFields.png|800px|Improving IBM CMOD Performance by Requiring Indexed Fields ]]
+==Caveats==
+As mentioned above, it's important to use indexes on fields carefully, to ensure maximum benefit at minimum cost in terms of storage space and time.  Also, the more Content Manager OnDemand Application Group fields that have indexing enabled, the more time and effort it takes to create, update, and maintain them, possibly slowing down the load process, and extending maintenance windows for arsmaint.
 = Usage =