About Ninox searching, indexing, search speed and file size

Ninox Profile

Under field properties what is the impact of checking or unchecking "Global search" and/or "Index"?

I realize that placing an index on a column will improve the speed of searches performed on that column, and increase the file size. If "index" is unchecked will that remove the column from global searches entirely, or just make them much slower as the number of records in the table increases?

Is the impact the same for local (ctrl & F key/cmd & F key) searches? 

Ninox Profile

@Westy.. what platform are you using?  Mac, iPad, Android, Web?   On the MacApp... I do not see an "index" option.  As for global search.. I have databases that have multiple tables with well over 100,000 rows each.. and see no noticable difference if tables / columns are in the global search. 

By design, I generally remove lookup / reference tables from global search. 

Ninox Profile

"Index" is not recommended for normal use but in some cases it is, Birger said we normally don't have to activate this.

I believe this is also still an experimental option (forgive me developers if i'm wrong).

"Global search" is wether you should find the result of that specific field back in the search command. In some cases this is not necessary, just think of recurring fields such as units (litres, kilograms, metres,...). This can be usefull to keep an overview in the search results and don't have redundant results.

Ninox Profile

I am using the Ninox Cloud.

Ninox Profile

@Westy, 

Ahh.. that is it..  Tough to keep up with all the platform differences.

cloudIndex

M

Just for an update reference (cloud version).

I have a table with currently 860,000 records in it (and approx 20 fields within each record).

 

Screenshot 2021-02-23 at 16.08.44

 

In playing with both the options to have one or more key fields indexed or not. I have done a considerable amount of checking re initial table load times and searches.

This includes different table views with different filter/sort criteria applied.

I have concluded so far that basically it makes no appreciable difference whether a field is indexed or not and I guess Steven may be right in that this option although shown may not yet be implemented!.

Note all testing was done with both wired internet connection and wireless (Broadband fibre 76mg download) and wireless.

On the basis that wireless is slower this is finding. (No Indexs set and using the wireless slower connection)

 

Initial table loading (approx 45 seconds) (providing the first view is in data ebtry ID order) - Can be up to 4 mins if a sorted view fis the first table

Once loaded - swopping to another view on same table - a few seconds

Searches (global) almost instant!

Searches (table) - again almost instant

Reloading after sizable batch update records cvs import done (approx20- k40 records) - anything up to 4 mins

So you have to exit the table onto another table and then reload the table! which presuably refreshes the table internally between Ninox and me!

also if you don't do this Nixonx seems to give up calculating any summary counts!  

Deleting selected filtered records (50 and 75 tested) from a button script! -  anything up to 6 mins!

and have to reload the table to make quick again

Imports with over circa 100,000 records to update caused failed timeouts

err acid test - doubling up the database to appro 1.2 million records

searches/mods load times all caused time out /connection issues (untested as wired)

Wired no doubt improves on all this !!!

At this record size it seem that in order to regain any spped etc after conduction som process that effects/updates/adds or deletes records

Then by closing the table and reloading it seems to do a bit of an internal reset!.

Database size 335mb (no images stored)

 

 

 

M

Just to add - on this table I had to do today 148 batch file imports (update to mod data)

after every couple of imports I then had to exit to table to allow Ninox to recal the actual record count and otherwise the next import would take 10 mins and sit ther tryaing to do its thing. (instant(ish) if i exit table and reload)

M

Applogies - Correct to above

that should read Initial Table loading (approx 1m.45 seconds) and not just 45 seconds

So give or take 2 minutes to load a table with 600k records

and just to clarify once loaded if you then go off that table (not closing the App) to another table and back again to reload 

it takes approx a few seconds (so its all about downloading/caching the table on first call)

So how to make this practical for me - Simple

I load 2 sessions and do everyday house keeping/records etc in the one session and leave the other session fully in that large table!

I guess that is the benefit of the cloud version

 

Reply