Subject Re: [firebird-support] Re: Firebird Indexing problem
Author Svein Erling Tysvær
Post date 2015-10-23T06:56:37Z
Hi, you're wrong. Non-indexed reads are faster than indexed reads. However, indexed reads may reduce the data that needs to be read and with the right conditions, it can speed up things tremendously. Let's say you have a table with breast cancer patients. Using an index for gender would slow things down if searching for women (99% of breast cancer cases are women), whereas it could be fairly useful for men (the remaining 1%).

Let's say you have 50 different values for usr, each of them equally much used in rep$log. Usr = 'REPL' will then match 2% of the records, and that means that using the index can eliminate the remaining 98% and using an index is sensible. Usr <> 'REPL' on the other hand will match 98% of the records and using the index will only eliminate 2%. Scanning the entire table using NATURAL will always be quicker than using an index in such cases.

I don't know where the threshold for when using an index is sensible or not, but am pretty certain it is somewhere between those 2% and 98%.

HTH,
Set

2015-10-23 8:40 GMT+02:00 drcih87@... [firebird-support] <firebird-support@yahoogroups.com>:


Hello Karol,
I don't think I understand you fully. Indexed reads a hudreds of times faster than non indexed. And the table will have millions of records, so I do need them.
"then this is cheaper to scan table (500 records) then use 400 indexed reads"
What do you mean scan table? How do I do that? Like a stored procedure with for select? I don't understand, please explain.