Mysql – Inner join on output of a subquery

join;MySQLself-join

We have Slot table which tells us about freetime for an employee between starttime and endtime.

We need to find Slot which have freetime > 'x' mins. This could be either be a complete slot or it can be consecutive slot for same employee(in this case we show the first slot to user).

We are using MySQL 5.7

  `CREATE TABLE `Slot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `freetime` time NOT NULL,
  `assigned_To_Id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=latin1

We tried following query :

Select        distinct S1.starttime, 
               S1.endtime 
from 
    (Select * from `Slot`
     where     `assigned_To_Id` in (2, 3, 4) 
     and       `freetime`  > 0 
     and       `starttime` >= '2018-08-08 00:00'
     and       `endtime`   <= '2018-08-08 23:59'
     order by `Slot`.`starttime` ASC) as S1

inner join S1 as S2
 on        (S1.freetime >'00:10:00') 
 or        (S2.starttime = S1.starttime + INTERVAL 60 MINUTE 
 and       ADDTIME(S1.freetime, S2.freetime) > '00:10:00'
 and       S1.assigned_To = S2.assigned_To)

We are getting stuck as to how to run self join on output of S1- its gives error saying that S1 table does not exist.

We read online and found about Common Table Expressions but we are using 5.7 version.

We are not sure how to go about doing inner join on same query.

Example :

|id| startime            | endtime             | assigned_To_Id | freetime
|1 | 2018-07-07 11:00:00 | 2018-07-07 12:00:00 |   2            | 00:00:05
|2 | 2018-07-07 12:01:00 | 2018-07-07 13:00:00 |   2            | 00:00:15
|3 | 2018-07-07 13:01:00 | 2018-07-07 14:00:00 |   2            | 00:00:05
|4 | 2018-07-07 15:01:00 | 2018-07-07 16:00:00 |   2            | 00:00:15

Output Should be :

|1 | 2018-07-07 11:00:00 | 2018-07-07 12:00:00 |   2            | 00:00:05
|2 | 2018-07-07 12:01:00 | 2018-07-07 13:00:00 |   2            | 00:00:15
|4 | 2018-07-07 15:01:00 | 2018-07-07 16:00:00 |   2            | 00:00:15

Best Answer

You can do all that without subqueries:

SELECT DISTINCT S1.starttime
     , S1.endtime 

  FROM Slot AS S1
  JOIN Slot AS S2  ON S2.assigned_To_Id = S1.assigned_To_Id
                  AND S1.assigned_To_Id IN (2, 3, 4) 
                  AND S1.id != S2.id -- a little trick to prevent joining rows with itself 
                                     -- slightly reduce the derivative table size 
                                     -- and speed up the overall performance

 WHERE S1.starttime >= '2018-08-08 00:00'
   AND S1.endtime <= '2018-08-08 23:59'
   AND S1.freetime > 0 
   AND ( S1.freetime > '00:10:00' 
         OR ( S2.starttime = S1.starttime + INTERVAL 60 MINUTE 
              AND ADDTIME(S1.freetime, S2.freetime) > '00:10:00' ))

 ORDER BY S1.starttime ASC
;

In fact aliasing is not transitive operation in the mysql. Say, simple query

SELECT 1 AS a, a AS b;

yeild an error Unknown column 'a' in 'field list'. So you can create an alias for the real object not for another alias.

Related Solutions

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Best Answer

Related Solutions

Mysql – Finding rows for a specified date range

Mysql – Adding index to large thesql tables

Related Question