Subject Re: [firebird-support] Help in optimizing big data query
Author Iwan Cahyadi Sugeng

Thank you for the explanation, will optimize index, i've tried to add index on TANGGAL_CETAK, and it really work faster and as expected, showing index read as much as needed only, not all record.

On Tue, Jun 18, 2019 at 11:33 AM liviuslivius liviuslivius@... [firebird-support] <> wrote:


If you have wher clause i that ordered query,
Think how you can do this on the paper. You have rows ordered you read first and interesting is not null, you must skip it and go to next. And if you must skip e.g 30 000 000 to read 1 000 000 then you see what is going on. Always your indexes should satisfy your where clause. Above sample show something called selectivity. You must maximize selectivity of index used. E.g. if you have only value true and false in the field then if your half of the records are true and other half false then index can only slowdown query execution. If you have 1000 records with true and 10000000 with false index can speed up query looking for true and slowdown query looking for false.

Sumarize, index slowdown common value.

Karol Bieniaszewski