Tag: records

bitcoincore development – What is in the Bitcoin Core LevelDB dbcache? Is it full records or metadata?

There are basically two separate uses: the block index which is essentially metadata and the UTXO database.

The dbcache is not really a cache, it’s a write buffer and it prevents needing to sync the disk or make random writes. As a cache it doesn’t do much and isn’t needed for that. It does also act as a cache, but if you nullify that benefit it’s only approximately a 10% slowdown even with a huge dbcache.

With a fast SSD (e.g. NVMe) I think the difference between a 400MB cache and a 5GB one is “only” a halving of IBD time (when syncing from LAN peers).

To prevent corruption the database updates must involve synchronizing writes.

We changed how dbcache flushing worked with the explicit intention of making background flushing possible so that it could be concurrently flushing and processing blocks constantly rather than inserting these bubbles where it waits on the disk. Even though the consistency requirements make that possible now (the UTXO database doesn’t have to be consistent except there is a record that states that all blocks before it have absolutely positively been applied to the database), actually making the change is still extremely complicated.

Every time you process a block, once the cache fills you could flush out to disk the remaining dirty entries for the oldest remaining blocks until it’s back under the limit again.. So essentially flushing every block in the background. Then every once in a while, you do a disk sync and update the record that says where the completely consistent point is, without having to flush anything additional out. So almost no latency spikes at runtime.

This would be good for mining but doing that requires a bunch of machinery to efficiently track things and probably would make sense to replace the dbcache’s map with an open hash table of some kind at the same time to cut the malloc traffic down by 10x. I expect there is a factor of 2 IBD speedup waiting from these changes. I’m not sure if it’ll ever happen though, it’s a big complicated task and any mistake is a consensus bug.

You can even do neat stuff like resolve collisions in the open hash table by displacing the oldest UTXO and once you’ve gone “far enough” flush. The table could run extremely close to completely full at all times.

I’m personally fond of cuckoo tables. Where every item has a small number of possible locations like two random buckets which each hold 4 items and if those locations are full you pick one, insert the item there, bump what was in that slot into one of its alternative locations.

If the table has a little slack (no more than ~95% full for 2 buckets of 4 items for example) then it’s always successful at finding an empty slot after a few kicks. Lookup is extremely fast since you only need to do two random memory accesses. There are a bunch of different designs though most are best when you can keep the table <50% full but the cuckoo design is easy to take up to arbritarily high fullness.

The STL sets that Bitcoin uses are a hash table, but collisions are resolved by having each entry in the hash table be a linked list, so every access involves several pointer chases and every insertion takes a malloc.

(This question was answered by gmaxwell on IRC. It has been paraphrased and any errors are my own.)

data tables – How to stop user loading more records on grid

In one legacy UI application on which I am working currently, has a Main List which is used to display all records stored in elastic in paginated way. Elastic may have more than 1 million records.

The problem arises when I added a functionality of loading next page of records when scroll touches bottom (no direct pagination widget).

In the introduced functionality of scroll load, user can scroll infinitely. for each “scroll at the bottom” event, data loads from 0 to pageNo*pageSize records. I set page size as small as 50 records. So, for example,

  • Page 1 will load 50
  • Page 2 – 100 records
  • Page 3 – 150 records
  • Page 4 – 200 records

and so on..

I want to acknowledge user after a certain page number like 20 pages(1000 records loaded on browser) that
Don’t go further otherwise application will become slow, use search with keywords instead.

Actually, I am doing so by putting simple auto hide alert which will start appearing from 20 pages onward and will display in interval of 5 pages like 20, 25,30 etc.

Is there any better way to acknowledge user that he is exploiting functionality because we are allowing him to do so?

I know this type of data loading sounds crazy but this is what we have to do. I need help on letting user know that application will be slow after a certain period of time.

Thanks in advance.

schema – Two records in the same table must be related somehow

I’ve inherited a somewhat strange table whose records must somehow be linked to each other. In the real world, these two “structures” are combined into one larger “structure”, so there should be a way of linking them.

Should I use an “association table” to link the two IDs, or is there a better way for this sort of thing? Perhaps I’ve answered my own question, but I thought I’d ask if there’s another way…

Another option would be to delete the second record, at the same time as I add a reference to that second record as a new column, in the first record.

Maybe this is a ridiculous open-ended question for this site, but surely I’m not the first person to run into this…

Thanks,
Sean

8 – Setting node ID sequence to a particular higher value for future created records

We have an organic groups based portal in D7 where many blood banks add records. We recently initiated Drupal 8 migration and are close to completion. However, we would want to add selective blood banks first to the new D8 portal, have it tested before we ask all the other blood banks to use the new portal.

Is it possible to set a higher NID value when new records are created by new users? Migration data for remaining customers can happen seamlessly as their NID values will be lower.

Thanks.

postgresql – Postgres group records by consecutive types

I would like to group objects by their consecutive start and end date.

+----------+----------------------------+
|  Fruit   | Time                       |
+----------+----------------------------+
| Apple    | 2020-09-08 00:00:00.000000 | 
| Apple    | 2020-09-08 01:00:00.000000 | 
| Orange   | 2020-09-08 02:00:00.000000 | 
| Orange   | 2020-09-08 03:00:00.000000 | 
| Apple    | 2020-09-08 04:00:00.000000 | 
+----------+---------------+------------+

The results should look like this:

+----------+----------------------------+----------------------------+
|  Fruit   | Start Time                 | End Time                   |
+----------+----------------------------+----------------------------+
| Apple    | 2020-09-08 00:00:00.000000 | 2020-09-08 01:00:00.000000 |
| Orange   | 2020-09-08 02:00:00.000000 | 2020-09-08 03:00:00.000000 |
| Apple    | 2020-09-08 04:00:00.000000 | 2020-09-08 04:00:00.000000 |
+----------+----------------------------+----------------------------+

postgresql – How to delete all records which are not referenced from other tables

I have a table to which a bunch of other tables has an FK reference. Is there any way of deleting records from this table only if they are not being referenced?

I know that I can left join the referencing table and check for null, but I have about 10 tables (more will be added) with FKs referencing this table, so it would be cool to have a generic way of doing it.

There are often not more than a handful of records I need to remove. I suppose I could loop and try to remove each record individually and protect each deletion with BEGIN/EXCEPT, but that is an ugly concept.

Does this kind of functionality exist in Postgres? Kind of a soft delete, or delete-if-allowed.

python – Parse selected records from empty-line separated file

This is my first post here and I hope I will get some recommendations to improve my code. I have a parser which processes the file with the following structure:

SE|43171|ti|1|text|Distribution of metastases...
SE|43171|ti|1|entity|C0033522
SE|43171|ti|1|relation|C0686619|COEXISTS_WITH|C0279628

SE|43171|ab|2|text|The aim of this study...
SE|43171|ab|2|entity|C2744535
SE|43171|ab|2|relation|C0686619|PROCESS_OF|C0030705

SE|43171|ab|3|text|METHODS Between April 2014...
SE|43171|ab|3|entity|C1964257
SE|43171|ab|3|entity|C0033522
SE|43171|ab|3|relation|C0085198|INFER|C0279628
SE|43171|ab|3|relation|C0279628|PROCESS_OF|C0030705

SE|43171|ab|4|text|Lymph node stations...
SE|43171|ab|4|entity|C1518053
SE|43171|ab|4|entity|C1515946

Records (i.e., blocks) are separated by an empty line. Each line in a block starts with a SE tag; the text tag always occurs in the first line of each block (in the 4th field). The program extracts:

  1. All relation tags in a block, and
  2. Corresponding text (i.e., sentence ID (sent_id) and sentence text (sent_text)) from the first line of the block, if relation tag is present in a block. Please note that the relation tag is not necessarily present in each block.

Below is a mapping dictionary between tags and related fields in a file and a main program.

# Specify mappings to parse lines from input file
mappings = {
        "id": 1,
        "text": {
            "sent_id": 3,
            "sent_text": 5
        },
        "relation": {
            'subject': 5,
            'predicate': 6,
            'object': 7,
        }
    }

Finally a code:

def extraction(file_in):
    """This function extracts lines with 'text' and 'relation'
    tag in the 4th field."""
    extraction = {}
    file = open(file_in, encoding='utf-8')
    bla = {'text': ()}
    for line in file:
        results = {'relations': ()}
        if line.startswith('SE'):
            elements = line.strip().split('|')
            pmid = elements(1)
            
            if elements(4) == 'text':
                tmp = {}
                for key, idx in mappings('text').items():
                    tmp(key) = elements(idx)
                bla('text').append(tmp)
            
            if elements(4) == 'relation':
                tmp = {}
                for key, ind in mappings('relation').items():
                        tmp(key) = elements(ind)
                tmp.update(sent_id = bla('text')(0)('sent_id'))
                tmp.update(sent_text = bla('text')(0)('sent_text'))
                results('relations').append(tmp)
                extraction(pmid) = extraction.get(pmid, ()) + results('relations')
        else:
           bla = {'text': ()}
    file.close()
    return extraction

The output looks like:

import json
print(json.dumps(extraction('test.txt'), indent=4))

{
    "43171": (
        {
            "subject": "C0686619",
            "predicate": "COEXISTS_WITH",
            "object": "C0279628",
            "sent_id": "1",
            "sent_text": "Distribution of lymph node metastases..."
        },
        {
            "subject": "C0686619",
            "predicate": "PROCESS_OF",
            "object": "C0030705",
            "sent_id": "2",
            "sent_text": "The aim of this study..."
        },
        {
            "subject": "C0085198",
            "predicate": "INFER",
            "object": "C0279628",
            "sent_id": "3",
            "sent_text": "METHODS Between April 2014..."
        },
        {
            "subject": "C0279628",
            "predicate": "PROCESS_OF",
            "object": "C0030705",
            "sent_id": "3",
            "sent_text": "METHODS Between April 2014..."
        }
    )
}

Thanks for any recommendation.

postgresql – postgreql 11: how to get result of a query in set of records and then process them in a function

I want to write a function like below.Whats wrong in it?

CREATE OR REPLACE FUNCTION myFunc(abc BIGINT, sss myTable.Col1%Type) AS 
DECLARE 
myRec RECORD;
tab_Rec RECORD(); --This i want to as a table (i dont know the column details though). 
--Declaring it as array fails

BEGIN
SELECT INTO myREC FROM public.myOtherFunc(abc, sss);

--Returns a table i.e. multiple rows and columns
SELECT * INTO tab_Rec FROM public.myAddFunc(abc);

--I want to do this now
for x in 1 .. array_length(tab_Rec, 1)
loop
         ---some logic
end loop;


END;

mysql – How can I get separate records when group_concat is applied

I have this huge query which used to get the details of orders rang and convert the price according to the currency.

SELECT *, 
        YEAR(o.date_added) AS year, 
        QUARTER(o.date_added) AS quarter,       
        MONTHNAME(o.date_added) AS month, 
        MIN(o.date_added) AS date_start, 
        MAX(o.date_added) AS date_end, 
        DAYNAME(o.date_added) AS day_of_week, 
        HOUR(o.date_added) AS hour, 
        (SELECT cgd.name FROM `ecom_customer_group_description` cgd WHERE cgd.customer_group_id = o.customer_group_id AND cgd.language_id = '1') AS customer_group, 
        (SELECT c.value FROM `ecom_currency` c WHERE c.currency_id = o.currency_id) AS currency_rate, 
        COUNT(o.order_id) AS orders, 
        COUNT(DISTINCT CONCAT(o.lastname, ', ', o.firstname)) AS customers, 
        SUM((SELECT SUM(op.quantity) FROM `ecom_order_product` op WHERE op.order_id = o.order_id GROUP BY op.order_id)) AS products, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'sub_total' GROUP BY ot.order_id)) AS sub_total, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'handling' GROUP BY ot.order_id)) AS handling, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id)) AS low_order_fee, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'shipping' GROUP BY ot.order_id)) AS shipping, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'reward' GROUP BY ot.order_id)) AS reward, 
        SUM((SELECT SUM(1/c.value  (-1 (Abs(ot.value)))) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id)) AS coupon, 
        SUM((SELECT SUM(ROUND(1/c.value * ot.value, 2)) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'tax' GROUP BY ot.order_id)) AS tax,   
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'credit' GROUP BY ot.order_id)) AS credit, 
        SUM((SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_currency` c WHERE ot.order_id = o.order_id AND c.currency_id = o.currency_id AND ot.code = 'voucher' GROUP BY ot.order_id)) AS voucher, 
        SUM((SELECT (1/c.value * o.total) FROM `ecom_currency` c WHERE c.currency_id = o.currency_id)) AS total, 
        SUM((SELECT SUM(1/c.value * (op.cost*op.quantity)) FROM `ecom_order_product` op, `ecom_currency` c WHERE op.order_id = o.order_id AND c.currency_id = o.currency_id GROUP BY op.order_id)) AS prod_costs, 
        SUM(o.commission) AS commission, 
        SUM(o.payment_cost) AS payment_cost, 
        SUM(o.shipping_cost) AS shipping_cost, 
        SUM(o.extra_cost) AS extra_cost,  GROUP_CONCAT('',o.order_id,'' ORDER BY o.order_id DESC SEPARATOR '
') AS order_ord_id, 
                    GROUP_CONCAT(o.order_id ORDER BY o.order_id DESC SEPARATOR '
') AS order_ord_idc,        
                    GROUP_CONCAT(DATE_FORMAT(o.date_added, '%d/%m/%Y') ORDER BY o.order_id DESC SEPARATOR '
') AS order_order_date, 
                    GROUP_CONCAT(IFNULL(o.invoice_prefix,'  '),IFNULL(o.invoice_no,'  ') ORDER BY o.order_id DESC SEPARATOR '
') AS order_inv_no, 
                    GROUP_CONCAT(CONCAT(o.firstname,' ',o.lastname,IF (o.payment_company = '','',CONCAT(' / ',o.payment_company))) ORDER BY o.order_id DESC SEPARATOR '
') AS order_name, 
                    GROUP_CONCAT(o.email ORDER BY o.order_id DESC SEPARATOR '
') AS order_email, 
                    GROUP_CONCAT(IFNULL((SELECT cgd.name FROM `ecom_customer_group_description` cgd WHERE cgd.customer_group_id = o.customer_group_id AND cgd.language_id = '1'),' ') ORDER BY o.order_id SEPARATOR '
') AS order_group,  
                    GROUP_CONCAT(IF (o.shipping_method = '','  ',o.shipping_method) ORDER BY o.order_id DESC SEPARATOR '
') AS order_shipping_method, 
                    GROUP_CONCAT(IF (o.payment_method = '','  ',o.payment_method) ORDER BY o.order_id DESC SEPARATOR '
') AS order_payment_method, 
                    GROUP_CONCAT(IFNULL((SELECT os.name FROM `ecom_order_status` os WHERE os.order_status_id = o.order_status_id AND os.language_id = '1'),'  ') ORDER BY o.order_id DESC SEPARATOR '
') AS order_status, 
                    GROUP_CONCAT(o.store_name ORDER BY o.order_id DESC SEPARATOR '
') AS order_store, 
                    GROUP_CONCAT(o.currency_code ORDER BY o.order_id DESC SEPARATOR '
') AS order_currency, 
                    GROUP_CONCAT(IFNULL((SELECT SUM(op.quantity) FROM `ecom_order_product` op WHERE op.order_id = o.order_id GROUP BY op.order_id),'  ') ORDER BY o.order_id DESC SEPARATOR '
') AS order_products, 
                    GROUP_CONCAT(IFNULL((SELECT ROUND(SUM(1/currency_rate*ot.value), 2) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0) ORDER BY o.order_id DESC SEPARATOR '
') AS order_sub_total, 
                    GROUP_CONCAT(IFNULL((SELECT ROUND(1/currency_rate*SUM(ot.value), 2) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'shipping' GROUP BY ot.order_id),0) ORDER BY o.order_id DESC SEPARATOR '
') AS order_shipping, 
                    GROUP_CONCAT(IFNULL((SELECT ROUND(1/currency_rate*SUM(ot.value), 2) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'tax' GROUP BY ot.order_id),0) ORDER BY o.order_id DESC SEPARATOR '
') AS order_tax, 
                    GROUP_CONCAT(ROUND(o.currency_value*o.total, 2) ORDER BY o.order_id DESC SEPARATOR '
') AS order_value, 
                    GROUP_CONCAT(ROUND(o.currency_value*(IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'handling' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'reward' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(-1 *(Abs(1/currency_rate*ot.value))) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'credit' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher' GROUP BY ot.order_id),0)), 2) ORDER BY o.order_id DESC SEPARATOR '
') AS order_sales, 
                    GROUP_CONCAT(ROUND(o.currency_value*(IFNULL((SELECT ROUND(1/currency_rate*SUM(op.cost*op.quantity), 2) FROM `ecom_order_product` op WHERE op.order_id = o.order_id),0)+o.commission), 2) ORDER BY o.order_id DESC SEPARATOR '
-') AS order_costs, 
                    GROUP_CONCAT(ROUND(o.currency_value*((IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'handling' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'reward' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(-1 *(Abs(1/currency_rate*ot.value))) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'credit' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher' GROUP BY ot.order_id),0))-(IFNULL((SELECT ROUND(1/currency_rate*SUM(op.cost*op.quantity), 2) FROM `ecom_order_product` op WHERE op.order_id = o.order_id),0)+o.commission)), 2) ORDER BY o.order_id DESC SEPARATOR '
') AS order_profit, 
                    GROUP_CONCAT(IFNULL(ROUND(100*(((IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'handling' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'reward' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(-1 (Abs(1/currency_rate*ot.value))) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'credit' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher' GROUP BY ot.order_id),0))-(IFNULL((SELECT ROUND(1/currency_rate*SUM(op.cost*op.quantity), 2) FROM `ecom_order_product` op WHERE op.order_id = o.order_id),0)+o.commission)) / (IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'sub_total' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'handling' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'low_order_fee' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'reward' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(-1 (Abs(1/currency_rate*ot.value))) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion') GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'credit' GROUP BY ot.order_id),0)+IFNULL((SELECT SUM(1/currency_rate*ot.value) FROM `ecom_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'voucher' GROUP BY ot.order_id),0))), 2),0) ORDER BY o.order_id DESC SEPARATOR '%
') AS order_profit_margin_percent,  (SELECT COUNT(o.order_id) FROM `ecom_order` o WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572') AS orders_total, 
        (SELECT COUNT(DISTINCT CONCAT(o.lastname, ', ', o.firstname)) FROM `ecom_order` o WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572') AS customers_total, 
        (SELECT SUM(op.quantity) FROM `ecom_order_product` op, `ecom_order` o WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND op.order_id = o.order_id) AS products_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'sub_total') AS sub_total_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'handling') AS handling_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'low_order_fee') AS low_order_fee_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'shipping') AS shipping_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'reward') AS reward_total, 
        (SELECT SUM(1/c.value  (-1 (Abs(ot.value)))) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND (ot.code = 'coupon' || ot.code = 'advanced_coupon' || ot.code = 'sales_promotion')) AS coupon_total, 
        (SELECT SUM(ROUND(1/c.value * ot.value, 2)) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'tax') AS tax_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'credit') AS credit_total, 
        (SELECT SUM(1/c.value * ot.value) FROM `ecom_order_total` ot, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND ot.order_id = o.order_id AND o.currency_id = c.currency_id AND ot.code = 'voucher') AS voucher_total, 
        (SELECT SUM(1/c.value * o.total) FROM `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id) AS total_total, 
        (SELECT SUM(1/c.value * (op.cost*op.quantity)) FROM `ecom_order_product` op, `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id AND op.order_id = o.order_id) AS prod_costs_total, 
        (SELECT SUM(1/c.value * o.commission) FROM `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id) AS commission_total, 
        (SELECT SUM(1/c.value * o.payment_cost) FROM `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id) AS pay_costs_total, 
        (SELECT SUM(1/c.value * o.shipping_cost) FROM `ecom_order` o, `ecom_currency` c WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' AND o.currency_id = c.currency_id) AS ship_costs_total 
        
        FROM `ecom_order` o WHERE (YEAR(o.date_added) = YEAR(CURDATE())) AND o.order_id >= '570' AND o.order_id <= '572' GROUP BY YEAR(o.date_added), MONTH(o.date_added) ORDER BY date_added DESC

Is there any way I can get all the records in different rows?
(1): https://i.stack.imgur.com/poKup.png
(2): https://i.stack.imgur.com/jvAbd.png

postgresql performance – Postgres tracking count of recent items in count table or by selecting count of recent records from history table

Which would generally be more efficient, or does it not matter?
Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has passed for that unique id.
Option 2 is not storing a count and fetching the count of records from a history table, by the records’ unique ids and within the past so many time units.
I would add an index on the id and timestamp fields for option 2.
The history table is in the low millions of records and doesn’t grow that fast.
Is the periodic vacuuming of the counts table likely to be much worse than selecting the count from the history table using indexed fields?
It seems like it would require less operations, no insert or update with Option 2.