0

Need some help with this query, I got it working with a SP but it's very slow and I simply cannot use it.

I have a table called cb_flights, that contains -

pk      ucid        takeoff_time    end_time        end_event       kills
3321598 1828a3b47   15:17:21        15:17:39        eject           1
3324418 1828a3b47   15:18:12        15:42:24        landing         2
3341913 1828a3b47   19:00:06        19:00:57        pilot_death     0
3342956 1828a3b47   19:08:45        19:08:56        landing         0
3345329 1828a3b47   19:09:23        19:31:42        landing         2
3346649 1828a3b47   19:38:17        19:38:34        landing         0
3348150 1828a3b47   19:40:01        19:48:39        pilot_death     1

This table currently shows only complete 2 lives. between pk 3324418 - 3341913 with 2 kills and between pk 3342956 - 3348150 with 3 kills.

I'm trying to get a result with the start and end times of the streak flights -

ucid        streak_start        streak_end      sum(kills)
1828a3b47   15:18:12            19:00:57        2
1828a3b47   19:08:45            19:48:39        3

I need the above table as interim to join with another table and get description of the kills. afterwards get the best streak for the ucid.

I have a SP that already does the best streak by the generic kills without combination with the other table, but it is very slow. The SP - 

CREATE DEFINER=`123`@`%` PROCEDURE `bestStreak`(in t_ucid VARCHAR(50))
BEGIN
DECLARE streakstart DATETIME;
DECLARE streakend DATETIME;
DECLARE streakresult INT;
DECLARE beststreak INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET streakresult = 0;

-- FROM FIRST SORTIE TO FIRST DEATH BEST STREAK
select min(takeoff_time) into streakstart
from cb_flights
where ucid = t_ucid;

select min(end_time) into streakend
from cb_flights
where end_event <> 'landing'
and ucid = t_ucid;

if streakresult is null then
    select sum(kills) into beststreak
    from cb_flights
    where takeoff_time >= streakstart 
    and takeoff_time < streakend
    and ucid = t_ucid
    group by ucid;
elseif streakresult = 0 then
    select sum(kills) into beststreak
    from cb_flights
    where takeoff_time >= streakstart 
    and ucid = t_ucid
    group by ucid;
end if;

-- FROM SECOND SORTIE TO LAST DEATH - EVALUATE ALL AND PUT IN BEST STREAK
beststrk: WHILE (streakstart<>streakend) DO

    select min(end_time) into streakstart
    from cb_flights
    where end_event <> 'landing'
    and end_time > streakstart
    and ucid = t_ucid;

    select min(end_time) into streakend
    from cb_flights
    where end_event <> 'landing'
    and end_time > streakstart
    and ucid = t_ucid;

    select sum(kills) into streakresult
    from cb_flights
    where takeoff_time between streakstart and streakend
    and ucid = t_ucid
    group by ucid;

    if streakresult > beststreak then
        select streakresult into beststreak;
    end if;        
END WHILE;

select beststreak;
END

Appreciate any help!

EDIT

Adding - show create table cb_flights

'CREATE TABLE `cb_flights` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `ucid` varchar(50) NOT NULL,
  `takeoff_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `end_event` varchar(45) DEFAULT NULL,
  `side` varchar(45) DEFAULT NULL,
  `kills` int(11) DEFAULT NULL,
  `type` varchar(45) DEFAULT NULL,
  `map_fk` int(11) DEFAULT NULL,
  `era_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `ucid_takeofftime` (`ucid`,`takeoff_time`),
  KEY `ucid_idx` (`ucid`) /*!80000 INVISIBLE */,
  KEY `end_event` (`end_event`) /*!80000 INVISIBLE */,
  KEY `side` (`side`)
) ENGINE=InnoDB AUTO_INCREMENT=7713276 DEFAULT CHARSET=utf8'
Morrtz
  • 47
  • 11

2 Answers2

0

Have you try to use INDEX? Indexes are used to retrieve data from the database very fast.

Sample INDEX

CREATE INDEX [YourTable_Index] 
ON YourTable (column1, column2, column3, column4) ON [PRIMARY]

Or you can use this below your BEGIN.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Buchiman
  • 320
  • 5
  • 18
  • The table is already indexed. It is indexed as follows - `pk - primary` `ucid + takeoff_time - unique` `ucid - index` `end_event - index` – Morrtz Dec 21 '18 at 07:33
  • Consider posting SHOW CREATE TABLE tbl_name; and SHOW INDEX FROM tbl_name; to avoid ongoing questions/answers, please. – Wilson Hauck Dec 22 '18 at 17:51
  • added, still looking for assistance with this. maybe a SP that will update another aggregated table. – Morrtz Dec 26 '18 at 21:44
  • You can do that in one SP just use a Mode. Example Mode = 1 is your Select and the Mode = 2 is your Update. – Buchiman Dec 27 '18 at 03:21
  • it is already in one SP. The issue is that it is very slow, I need assistance to get it working faster, much faster. – Morrtz Dec 27 '18 at 19:41
  • Try this is your SP Put it Below BEGIN, `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED` – Buchiman Dec 28 '18 at 00:28
0

I found the answer in these following links:

auto generating consecutive IDs for each group - http://www.mysqltutorial.org/mysql-row_number/

using these 2 stack overflow threads to understand how to work with different records - https://superuser.com/questions/1198455/use-mysql-to-select-the-next-one-record-after-a-matching-value https://superuser.com/questions/1198483/use-mysql-to-select-the-next-one-record-after-a-matching-value-without-a-sequent

Hope this helps someone who finds himself in this thread.

Morrtz
  • 47
  • 11