What is a database index and how to create one

Go to solution
Takumi Togashi
Tera Expert

‎08-15-2023 10:07 PM

We have created a column named "aaaa" in the table and created the database index shown below.

TakumiTogashi_0-1692162239510.png

 

After creation, I activated the unique of the column "aaaa" and the index column changed to "u_aaaaa_2" as shown in the figure below.

TakumiTogashi_2-1692162358088.pngTakumiTogashi_3-1692162370669.png

Why has this changed? Is there any problem?
Also, I would like to know how to use database indexes since I don't understand how to use them in the first place.

0 Helpfuls
  • 7,130 Views
1 ACCEPTED SOLUTION

Go to solution
Community Alums
Not applicable

‎08-15-2023 10:33 PM

Hi @Takumi Togashi ,

Imagine you need to search for a Keyword in a Book, and you don't have an Index at the end of the Book?

- Only way to find the Keyword is to search the complete Book which takes Minutes even Hours!

If you have an Index in place(which usually stays at the end of the Book), the search will be faster, i.e. in seconds.

The ultimate goal of Indexing -

- Indexes will improve the Query/SQL performance(i.e. GlideRecord Query Response Times) in Scripts.
- Indexes will improve searches on Lists/ Form load times.
- Global search
- Reports/ Homepages/ Dashboards

Refer to Create a table index 

DO's

  1. Always test your indexes in a sub production instance first and see if it helps improve performance before applying in production.
  2. Always consider using "active" in your filter condition as part of your business logic. This enables the query to process on a smaller result set and helps improve query response time.
  3. Use "is", "starts with" in your filter/searches wherever possible with "AND" in the query.
  4. Date fields such as "sys_created_on" or "sys_updated_on" should always be considered in filters/searches which enhances the performance of the query.
  5. Adding a composite/compound index on single large flattened tables to the "active" and "date_time" fields is bound to give you improved query response time if those are the two fields being used in the query.

 

DO NOT's

  1. Indexes occupy space on disk in memory buffers and there can only be 64 INNODB MySQL indexes per table. Hence do not index every field as that defeats the purpose.
  2. It is not recommended to index data type such as TEXT, MEDIUM TEXT, LONG TEXT if your search string is going to be long. These are fields which can hold multibyte characters such as short description, comments, and work-notes. Index on these fields may not useful because it only indexes first few characters. But if your search string is small for example you are searching for incidents with"memory low" the index could be beneficial.
  3. Avoid using "contains" , "OR's" in your filters/searches wherever possible as the MySQL optimizer may end up doing a full table scan.

 

View solution in original post

2 REPLIES 2

Go to solution
Community Alums
Not applicable

‎08-15-2023 10:33 PM

Hi @Takumi Togashi ,

Imagine you need to search for a Keyword in a Book, and you don't have an Index at the end of the Book?

- Only way to find the Keyword is to search the complete Book which takes Minutes even Hours!

If you have an Index in place(which usually stays at the end of the Book), the search will be faster, i.e. in seconds.

The ultimate goal of Indexing -

- Indexes will improve the Query/SQL performance(i.e. GlideRecord Query Response Times) in Scripts.
- Indexes will improve searches on Lists/ Form load times.
- Global search
- Reports/ Homepages/ Dashboards

Refer to Create a table index 

DO's

  1. Always test your indexes in a sub production instance first and see if it helps improve performance before applying in production.
  2. Always consider using "active" in your filter condition as part of your business logic. This enables the query to process on a smaller result set and helps improve query response time.
  3. Use "is", "starts with" in your filter/searches wherever possible with "AND" in the query.
  4. Date fields such as "sys_created_on" or "sys_updated_on" should always be considered in filters/searches which enhances the performance of the query.
  5. Adding a composite/compound index on single large flattened tables to the "active" and "date_time" fields is bound to give you improved query response time if those are the two fields being used in the query.

 

DO NOT's

  1. Indexes occupy space on disk in memory buffers and there can only be 64 INNODB MySQL indexes per table. Hence do not index every field as that defeats the purpose.
  2. It is not recommended to index data type such as TEXT, MEDIUM TEXT, LONG TEXT if your search string is going to be long. These are fields which can hold multibyte characters such as short description, comments, and work-notes. Index on these fields may not useful because it only indexes first few characters. But if your search string is small for example you are searching for incidents with"memory low" the index could be beneficial.
  3. Avoid using "contains" , "OR's" in your filters/searches wherever possible as the MySQL optimizer may end up doing a full table scan.

 

Go to solution
Minh Huy Lam Qu
Giga Sage

‎08-19-2023 08:49 AM

You can follow this video to understand more about indexes 

Regards,

Huy Lam