| Subject | Re: [firebird-support] Help in optimizing big data query |
|---|---|
| Author | Iwan Cahyadi Sugeng |
| Post date | 2019-06-17T11:51:51Z |
Hi everyone,
------ QUERY PERFORMANCE ------
Prepare : 94 ms
Execute : 00:40:53
Avg fetch time: 0 ms
----------- MEMORY ------------
Current : 54.89 MB
Max : 57.19 MB
Buffers : 2048
------ TABLE OPERATIONS -------
Table name : TBAT_ITM
Idx reads : 56549278
Non-idx reads : 0
Updates : 0
Deletes : 0
Inserts : 0
I now have about 55 million data on this table, the structure is on the first thread, i'v added index on NOMOR, the query is :
SELECT
TBAT_ITM.NOMOR
FROM TBAT_ITM
ORDER BY TBAT_ITM.NOMOR
ROWS 1 TO 1000000
------ QUERY PERFORMANCE ------
Prepare : 94 ms
Execute : 00:40:53
Avg fetch time: 0 ms
----------- MEMORY ------------
Current : 54.89 MB
Max : 57.19 MB
Buffers : 2048
------ TABLE OPERATIONS -------
Table name : TBAT_ITM
Idx reads : 56549278
Non-idx reads : 0
Updates : 0
Deletes : 0
Inserts : 0
I expect the query is using NOMOR as the index and of course don't have to read all the record just to order them, but the query plan is using ORDER and it read all 55 millions records to get 1 millions records? Is this as design?
Thank you
On Tue, Apr 23, 2019 at 11:45 AM Iwan Cahyadi Sugeng <iwan.c.sugeng@...> wrote:Hi,The data is repeated and most records are empty value, it will be filled after a process. But will try to add index on itThank youOn Tue, Apr 23, 2019 at 11:35 AM liviuslivius liviuslivius@... [firebird-support] <firebird-support@yahoogroups.com> wrote:HiAs you can see, your query do not use any index for filter records. You got indexed reads only for order by. The obvius question is what about creating index on e.g KODE_MESIN.Regards,Karol Bieniaszewski