Mysql – fastest way for getting concurrent entries in large database

You’ll need to generate rows for every minuute in the day in order to get all active session ids in every minute. Using recursive ctes could be helpfull here:

--      S a m p l e    D a t a : 
CREATE TABLE `session_id_table` (
  `session_id` bigint NOT NULL,
  `session_start_time` datetime DEFAULT NULL,
  `session_end_time` datetime DEFAULT NULL,
  PRIMARY KEY (`session_id`),
  UNIQUE KEY `session_id_UNIQUE` (`session_id`),
  KEY `idx_session_id_table_session_id` (`session_id`),
  KEY `time_1` (`session_start_time`,`session_end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;

insert into session_id_table(session_id, session_start_time, session_end_time) values 
(1, '2024-09-05 10:00:23', '2024-09-05 10:15:54'), 
(2, '2024-09-05 10:00:29', '2024-09-05 10:22:54'), 
(3, '2024-09-05 10:01:23', '2024-09-05 10:26:54'), 
(4, '2024-09-05 10:02:20', '2024-09-05 10:21:54'), 
(5, '2024-09-05 10:02:23', '2024-09-05 10:20:54'),
(6, '2024-09-05 10:21:00', '2024-09-05 10:35:54');

WITH    -- C T E (s)
  recursive log_hours  As
    ( Select 0 as hrs 
     Union All
      Select hrs + 1 as value
      From log_hours
      Where  log_hours.hrs < 23
    ),
  hrs_mins AS
    ( Select  Concat( LPAD(h.hrs, 2, '0'), ':', LPAD(m.mins, 2, '0') ) as hr_min 
      From    log_hours h
      Left  Join ( WITH 
                     recursive log_mins  As
                       ( Select  0 as mins 
                        Union All
                         Select  mins + 1 as value
                         From    log_mins
                         Where   log_mins.mins < 59
                       )
                   Select * From log_mins
                 ) m ON ( 1 = 1 )
      Order By  h.hrs, m.mins
    ), 
  dates AS
    ( Select   CONVERT( DATE_FORMAT(s.session_start_time,'%Y-%m-%d'), DATE ) as dt,
               Min(CONVERT(DATE_FORMAT(s.session_start_time,'%Y-%m-%d-%H:%i:00'), DATETIME)) as min_dt_tm,
               Max(CONVERT(DATE_FORMAT(s.session_end_time,'%Y-%m-%d-%H:%i:00'), DATETIME)) as max_dt_tm
      From     session_id_table s
      Group By CONVERT( DATE_FORMAT(s.session_start_time,'%Y-%m-%d'), DATE )
    )

--      M a i n    S Q L : 
Select       s.session_id, 
             Date_Format(s.session_start_time, '%Y-%m-%d') as session_date,
             hm.hr_min as session_hour_minute
From         dates d
Inner Join   hrs_mins hm ON( 1 = 1 )
Inner Join   session_id_table s ON( CONVERT( DATE_FORMAT(s.session_start_time,'%Y-%m-%d'), DATE ) = d.dt And 
                                    hm.hr_min >= Date_Format(s.session_start_time, '%H:%i') And 
                                    hm.hr_min <= Date_Format(s.session_end_time, '%H:%i')
                                  )
Where      CONVERT( Concat(DATE_FORMAT(d.dt,'%Y-%m-%d'), ' ', hm.hr_min), DATETIME ) >
             ( Select min_dt_tm From dates Where dt = CONVERT(DATE_FORMAT(s.session_start_time, '%Y-%m-%d'), DATE) ) And
           CONVERT( Concat(DATE_FORMAT(d.dt,'%Y-%m-%d'), ' ', hm.hr_min), DATETIME ) <=
             ( Select max_dt_tm From dates Where dt = CONVERT(DATE_FORMAT(s.session_start_time, '%Y-%m-%d'), DATE) )
Order By   Date_Format(s.session_start_time, '%Y-%m-%d'), hm.hr_min, s.session_id

/*      R e s u l t :
session_id  session_date    session_hour_minute
----------  --------------  -------------------
         1  2024-09-05      10:01
         2  2024-09-05      10:01
         1  2024-09-05      10:02
         2  2024-09-05      10:02
         3  2024-09-05      10:02
......      .......         .....
         2  2024-09-05      10:16
         3  2024-09-05      10:16
         4  2024-09-05      10:16
         5  2024-09-05      10:16
......      .......         .....
         2  2024-09-05      10:22
         3  2024-09-05      10:22
         6  2024-09-05      10:22
         3  2024-09-05      10:23
         6  2024-09-05      10:23
......      .......         .....        */

Addition:
First, ( @asd asd ) I’d like to thank you for your correction of my initial typos – been working on something similar (not same) and did some copy-paste actions without (or with incomplete) corrections. Thanks.

It is important to say that I’m not, in any way, someone who has some special knowledge about mysql – quite the opposite, I’ve never done a single job using mysql – the most of my jobs were (and are) tied to Oracle. On the other hand – the rdbms engines, no metter how different they could be, have a good deal of common things…

The answer to the question in coments why is this code faster than the one from question – well, there are a fair few of the reasons which you can find listed below and check using the link to the fiddle at the end. The list is not sorted in any order of significance and should be taken as a collection of some general rules and practices that were used in this answer to make this thing working a bit faster:

• the code is adjusted to use the indexes provided in OP’s create table statement
• cte(s) used, with the exception of dates cte, are completely separated from the data – no tables were used (see the fiddle below)
• recursion for hours and nested recursion for minutes generate a row for every minute in a day which is used as a predefined grid of rows for any posible date/day/period that exist in your data
• code doesn’t depend on implicite datatype conversions – if there is a date comparison needed – it is compared to another date datatype explicitely – timestamp to another timestamp
• all together, properly combined, made the code running a bit faster then the code from the question – the actual data from your table are processed with already prepared grid of all minutes in a day cross joined to all dates of interest with no conditions whatsoever ON(1=1).

EXPLAIN plans for every part/step of the code used in this answer could be seen in the fiddle here.