0

I have a table that holds the configuration for some operation in my system. This operation can only be performed monthly, so my configuration table has a column for month and another column for year and it also points to a client (every client has a list of configurations). Whenever there is a change in the configuration, I add a new entry to my table with a new month and a new year and that is the active configuration from that time and on. For example: I have a configuration for 1-2020 with id=1 and another configuration for 5-2020 with id=2. If I run the operation for 3-2020 the active configuration would be id=1 because 3-2020 is smaller than 5-2020 but bigger than 1-2020.

I use this select to find the configuration for a client in a given month and year:

SELECT * 
FROM configuration_table ct 
WHERE CONCAT(ct.year, LPAD(ct.month, 2, '0')) =
            (SELECT MAX(CONCAT(ct.year, LPAD(ct.month, 2, '0'))) 
             FROM configuration_table ctInner 
             WHERE (ctInner.year < :year 
                    OR 
                    (ctInner.month <= :month 
                        AND 
                    ctInner.year = :year)
                    ) 
             AND ctInner.client_id = client.id
            );

My question is: if I have a large list of clients, I have to loop them and do this select for each one, which is very slow. I want to pass a list of client ids as parameter and bring the configuration_table entry for all of them at once, respecting the month and year.

I know this is a very specific question, but I am at a loss on how to do it. Any suggestion is appreciated, even the ones that change my model completely, as long as it serves the same purpose.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Danilo Leite Ribeiro
  • 87
  • 5
  • Doesn't this sql you shared bring back every client? So wouldn't a WHERE clause on the main `SELECT` with your list of clients solve the issue?I feel like I'm misunderstanding your question. – JNevill May 24 '22 at 14:35
  • Is there a different maximum month for each client, or do you get the highest month for everyone and then use this to get the rows for each client? – Barmar May 24 '22 at 15:08
  • Each client has a different month. For ex: client 1 has a config in January 2020, so for March this is the one active. Another client however has a config for February 2020, so for a March request this is the relevant one. The SQL I shared does not bring every client back because in the inner where clause I put client_id = client.id – Danilo Leite Ribeiro May 24 '22 at 19:05
  • It is usually better to use a `DATETIME` than split up year and month into separate columns. – Rick James May 25 '22 at 20:44

0 Answers0