Will uniqueid() as a key impact performance speed when indexing a database with large data?

[Muhammad_Salooj]

Will uniqueid() as a key impact performance speed when indexing a database with large amounts of data and many references? 

[SkrOYC]

Idk why would it

[Muhammad_Salooj]

I'm asking if it will since its random and not incremental. I'm Not sure if I understood correctly while trying to learn about indexing in sql and the course instructor said that if indexes are not set up correctly, it will increase required server resources..

I don't know much about this. Appreciate any guidance. 

[SkrOYC]

Maybe @Marc_Dillon could help, as well as @Jonathon @WillowMobileSys @1minManager 

[1minManager]

Since appsheet assume the key values to be random, putting them in incremental order will give zero benefits

[Marc_Dillon]

Only in the case of using a Security Filter that could have benefitted from an appropriate index. You certainly can't specify which index to use from within Appsheet, so you'd have to rely on primary key primary index. I wonder if any database variant is able to auto-choose a suitable non-primary index, based on the select statement provided. That'd be cool, but I doubt it.

[WillowMobileSys · Accepted solution]

As I understand your question, you want to know if a randomly generated value of 8 characters will be less efficient for database indexing than a sequentially assigned value.  Correct?

The answer is no...there would be NO impact to indexing on the database side.   Once the UNIQUE() value is assigned, it is simply an 8 character value used as an ID or key.  When a database applies its indexing algorithm its has no idea if the value it's indexing is a sequential number in the correct order or not. 

All "indexing" means is that the database is applying a predetermined sort that is maintained as rows are inserted and deleted.  That way the database does not need to spend the time to re-sort data each time queries are made.  Primary keys are typically automatically indexed.  BUT, the physical order of the rows is not changed.  Instead there is a "lookup" table created where pointers to the rows are what is actually being sorted/indexed.  This allows for multiple indexes on the same table.

Now, one could argue that sorting 8 character values takes longer than re-sorting a list of sequential numbers that are only a few digits.  And you would be right.  But that it is insignificant for 2 reasons:

• The difference in time to sort these two types of values is negligible.
• Index sorting happens on the database in the background. 

One important point from AppSheet perspective...it is near impossible, if not impossible, to assign sequentially ordered key values to rows in an AppSheet app IF the app is being used by more than a single person adding rows.

[Muhammad_Salooj]

Thank you for the thorough explanation. This fully answers my questions. 🙂