Hi, The plan is using ORDER, it read through index, in my system, i need to load 1 millions of records and i only expect the server to read 1 millions of records only, not all the data in the table. I already use high speed m2 SSD Samsung EVO 860 series, it still need a lot of time to load. The problem is why firebird need to read all data in the table to give me 1 million of data I've been investigating more on this, i've tried to use this query : SELECT TBAT_ITM.NOMOR, ASCII_CHAR(13) || ASCII_CHAR(10) FROM TBAT_ITM WHERE TBAT_ITM.TANGGAL_CETAK IS NULL ORDER BY TBAT_ITM.NOMOR ROWS 1 TO 1000
This should be return 1000 records only, it reads all the records too and return only 1000 record, i think the problem is with the TANGGAL_CETAK not being indexed, i did not create index on it, but should i need to create an index on all column needed in the where clause? i've tried this query too SELECT TBAT_ITM.NOMOR, ASCII_CHAR(13) || ASCII_CHAR(10) FROM TBAT_ITM WHERE TBAT_ITM.KODE_MESIN IS NULL ROWS 1 TO 1000 i've added index on KODE_MESIN, and this query result 1000 index read, so i see that i need to add index on every field where needed by the where clause, any suggestion for best practise or something i need to consider How is firebird handle index? When new data inserted in a table with an index, should i still need to do reindex when a lot new data inserted, let say 50 millions of data inserted over time? Thank you On Mon, Jun 17, 2019 at 9:35 PM liviuslivius liviusliv...@poczta.onet.pl [firebird-support] <firebird-support@yahoogroups.com> wrote: > > > If the plan include ORDER not SORT then it read data throught index. > But if you are asking server about 1 000 000 records, what do you expect > more from the server to do? > And you use 2048 page buffers then all data is retrived from your HDD. To > speed up you must: > 1. Change design to retrive limited number of fecords e.g. 1000. > 2. Increase page buffers to speedup second and next queries. > 3. Invest in faster HDD like m2 3500 MB/s > > > Regards, > Karol Bieniaszewski > > >
