Performance improvement with large table scans

[abose]

Currently, the scan APIs like getFromNonIndex and query API has significant performance issues when working with large tables of >1M rows and with paging. Observations:

• Change name of getFromNonIndex to scan to be more explicit about the scan. #142
• Throttle requests to prevent spikes from crashing the service #143
• Allow getFromIndex queries to run with optional non index attributes. Similar to query API. #144
• Consider** order by clause with limit clause to improve pagination performance. We can either use the already existing primary key documentID or a new column with integer numbers to do the ordering. Surprisingly, the primary key based ordering gave more speed than integer column. this could be some optimization issue as I don't know how a 4-byte integer compares can be lower than a 16-byte string compare theoretically. documentID is random ordering; we should have a more predictable ordering column if we are going to use ordering.
• **not using order by seems to offer the highest performance for duration, but worse fetch penalty in the tests. ie, the current way of doing things.

measurements of pagination performance with scan

Number of rows in table

Row Count	Size
1,194,515	2.8GB

measurement with an integer column

SELECT * FROM phcode_extensions_dev.extensionDetails order by idx limit 100000,100;

3	50	16:19:52	SELECT * FROM phcode_extensions_dev.extensionDetails order by idx limit 100000,100	100 row(s) returned	0.687 sec / 0.079 sec
3	51	16:19:54	SELECT * FROM phcode_extensions_dev.extensionDetails order by idx limit 100000,100	100 row(s) returned	0.719 sec / 0.062 sec
3	52	16:26:44	SELECT * FROM phcode_extensions_dev.extensionDetails order by idx limit 100000,100	100 row(s) returned	0.703 sec / 0.078 sec
3	53	16:26:50	SELECT * FROM phcode_extensions_dev.extensionDetails order by idx limit 100000,100	100 row(s) returned	0.703 sec / 0.078 sec

measurement with documentID

SELECT * FROM phcode_extensions_dev.extensionDetails order by documentID limit 100000,100;

3	54	16:28:48	SELECT * FROM phcode_extensions_dev.extensionDetails order by documentID limit 100000,100	100 row(s) returned	0.344 sec / 0.078 sec
3	55	16:28:49	SELECT * FROM phcode_extensions_dev.extensionDetails order by documentID limit 100000,100	100 row(s) returned	0.344 sec / 0.078 sec
3	56	16:28:51	SELECT * FROM phcode_extensions_dev.extensionDetails order by documentID limit 100000,100	100 row(s) returned	0.329 sec / 0.078 sec
3	57	16:28:53	SELECT * FROM phcode_extensions_dev.extensionDetails order by documentID limit 100000,100	100 row(s) returned	0.344 sec / 0.078 sec

measurement with using no order-by clause

3	81	16:40:02	SELECT * FROM phcode_extensions_dev.extensionDetails limit 100000,100	100 row(s) returned	0.343 sec / 0.266 sec
3	82	16:40:04	SELECT * FROM phcode_extensions_dev.extensionDetails limit 100000,100	100 row(s) returned	0.328 sec / 0.187 sec
3	83	16:40:06	SELECT * FROM phcode_extensions_dev.extensionDetails limit 100000,100	100 row(s) returned	0.344 sec / 0.141 sec
3	84	16:40:12	SELECT * FROM phcode_extensions_dev.extensionDetails limit 100000,100	100 row(s) returned	0.328 sec / 0.109 sec

• investigate why the integer based index performs worse than a string based index, also investigate if there is a faster way to do this.
• not using order by seems to offer the highest performance for duration, but worse fetch penalty in the tests