best practice generate statics on user activty with MySQL and PHP-CodePudding

i developed a Laminas application with Mysql backend. Into the DB i've this table

storicoattivita

| id | dataAttivita | idUser | idAgenzia | idImmobile | idRichiesta | tipoAttivita | testo | idNuovoStatoImmobile | idNuovoStatoRichiesta | idStatoRichiesta |
| -- | ------------ | ------ | --------- | ---------- | ----------- | ------------ | ----- | -------------------- | --------------------- | ---------------- |
| 1  | 2022-02-01   | 2      | 1         | 150        | 350         | 1            | xxxxx | NULL                 | NULL                  | 2                |
| 2  | 2022-02-02   | 4      | 2         | NULL       | 350         | 2            | xxxxx | NULL                 | NULL                  | 2                |

... ...

in which I store a record for each activity that a user carries out on the "Immobile", "Richiesta" or both entities; each activity can be either a simple update to keep track of an activity carried out or a change in the status of the entities involved. What I should do is some statistics on this table, where for each user I want to know in the current month how many activities he has done of a certain type. the types of statistics can therefore be:

both on the simple value of the "tipoAttivita" field
both on certain combinations of "idNuovoStatoImmobile" and "idNuovoStatoRichiesta", for example when "idNuovoStatoImmobile" = 5 and "idNuovoStatoRichiesta" = 1
ecc

One way I've thought of doing this is to take the result of this query

SELECT st.*,
    FROM storicoattivita as st
    JOIN richieste as r on r.id=st.idRichiesta
    JOIN immobili as i on i.id=st.idImmobile
    
    where st.deleted=0 and r.deleted=0 and i.deleted=0
        and st.dataAttivita between '2022-01-01' and '2022-01-31'
        and ( 
            (st.idNuovoStatoRichiesta=1 AND st.idNuovoStatoImmobile=5 AND st.idImmobile is not null) 
            OR (st.idNuovoStatoRichiesta=4 AND st.idImmobile is not null) 
            OR  st.tipoAttivita > 0 
            )

and through PHP process the resultset line by line, save step by step the data in an associative array where the key is the user id, and then make the entries in the statistics table. The problem is that the "activity history" table is very large, processing so much data in php step by step seems like a bad practice to me. Also in terms of scalability in the future, then I see it as complicated to manage. I can't find an effective way to do this directly, perhaps, with something on Mysql.

Can you give me some advice or help?

thank you

CodePudding user response：