Stored procedure is too slow in mysql

Question

I have a routine. But it' s too slow. How can I improve the query?

My records: http://www.sqlfiddle.com/#!9/14cceb/1/0

My query:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE example.ssa()
BEGIN
drop table if exists gps_table;
drop table if exists exam_datas;
CREATE TEMPORARY TABLE gps_table(ID int PRIMARY KEY AUTO_INCREMENT,timei 
int, 
trun_date_time datetime, tadd_meter int, tin_here int null);
insert into gps_table(timei,trun_date_time,tadd_meter,tin_here) select 
imei, run_date_time, add_meter, in_here from example_table;
CREATE TEMPORARY TABLE exam_datas(ID int PRIMARY KEY AUTO_INCREMENT,vimei 
int, vbas_run_date_time datetime, vbit_run_date_time datetime, vdifff int);

select tin_here from gps_table limit 1 into @onceki_durum;
select count(id) from gps_table into @kayit_sayisi;
set @i = 1;
set @min_mes = 0;
set @max_mes = 0;
set @frst_id = 0;
set @imei = 0;
set @run_date_time = '0000-00-00 00:00:00';
set @run_date_time2 = '0000-00-00 00:00:00';

   myloop: WHILE (@i <= @kayit_sayisi) DO 
  select tin_here from gps_table where id = @i into @in_here_true;
  if (@in_here_true = 1) then
    select id,trun_date_time, tadd_meter from gps_table where id = @i into @frst_id,@run_date_time2, @min_mes;    
    select id from gps_table where id > @frst_id and tin_here =0 order by id asc limit 1 INTO @id; 
    SET @id = @id-1;                                                                                 
    select id, timei, trun_date_time, tadd_meter from gps_table                                          
    where id = @id and tin_here =1 limit 1 into @i, @imei, @run_date_time, @max_mes;                    

      if(@i-@frst_id>3) then
        set @i:=@i+1;
        insert into exam_datas(vimei,vbas_run_date_time,vbit_run_date_time,vdifff) Values (@imei, @run_date_time2, @run_date_time, @max_mes-@min_mes);
        SELECT * FROM exam_datas;
        SET @asd =1;
      elseif 1=1 then
        set @i:=@i+1;
        End if;
  ELSEIF 1=1
    THEN SET @i:=@i+1;
  End if;
  IF (@i = @kayit_sayisi) 
    THEN set @tamam =1; LEAVE myloop;
  END IF;
END WHILE myloop;  

select DISTINCT * from exam_datas;


drop table if exists exam_datas;
drop table if exists gps_table;
END

I need: id= 6 first true and id= 11 last_true

firs_trure - last_true = 304-290= 14

id=14 first true and id=18 last_true

firs_true - last_true = 332-324= 8

This routine is too slow.

MySql version is 5.7 and There are 2 milions record in the table.

UPDATE:

Query is here. HERE

Thank you @LukStorms

Whatever you do it looks like it is achievable to rewrite it as set-based. Processing row by row is not effective approach for SQL. — Lukasz Szozda, Jan 23 '19 at 16:15
Would you like to explain what you are trying to do or would you like us to reverse engineer your (dodgy) code to figure it out? — P.Salmon, Jan 23 '19 at 16:16

LukStorms · Accepted Answer · 2019-01-24T07:27:04.140

0

It's possible to get such results in 1 query.
Thus avoiding a WHILE loop over records.

This example works without using window functions. Just using variables inside the query to calculate a rank. Which is then used to get the minimums and maximums of the groups.

select
 imei, 
 min(run_date_time) as start_dt,
 max(run_date_time) as stop_dt, 
 max(add_meter) - min(add_meter) as diff
from
( 
  select imei, id, run_date_time, add_meter, in_here, 
  case
  when @prev_imei = imei and @prev_ih = in_here then @rnk
  when @rnk := @rnk + 1 then @rnk
  end as rnk,
  @prev_imei := imei as prev_imei,
  @prev_ih := in_here as prev_ih
  from example_table t
  cross join (select @rnk := 0, @prev_ih := null, @prev_imei := null) vars
  order by imei, id, run_date_time
) q
where in_here = 1 
group by imei, rnk 
having count(*) > 4
order by imei, min(id);

In the procedure such query can be used to fill that final temporary table.

A test on db<>fiddle here

edited Jan 24 '19 at 07:27

answered Jan 23 '19 at 21:11

LukStorms

19,080
3
26
39

I tried a similar query. But I didn't use cross join and having. Thank you so much. – Mikail Boybeyi Jan 24 '19 at 06:05
@MikailBoybeyi It's actually just a method to initialize the variables inside the query. To prevent they already have values from previous code. Setting them before running the query without cross join would also work. – LukStorms Jan 24 '19 at 06:50
Btw, the HAVING criteria was changed in the answer. Seems less confusing to use a `COUNT` instead of `MAX - MIN`. – LukStorms Jan 24 '19 at 09:11
Okey thank you again. Can I gather the meters between max and min in the same query? – Mikail Boybeyi Jan 24 '19 at 10:44
@MikailBoybeyi I assume you mean listing them into a string. You can use `GROUP_CONCAT` for that. An SO example of that [here](https://stackoverflow.com/questions/3083499/) – LukStorms Jan 24 '19 at 10:52
Sorry my English is so bad. I want to sum(add_meter) between min and max. So Can I use "sum(add_meter)" instead of max(add_meter) - min(add_meter). @LukStorms – Mikail Boybeyi Jan 24 '19 at 10:57
@MikailBoybeyi Well yes, there's that GROUP BY in the outer query. So you can use any [aggregate function](https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-in-mysql.php) you need. What wouldn't work is using such function inside such function. F.e. `SUM(MAX(x)+MIN(x))` would fail. It's grouped by `imei, rnk `, so a `SUM(add_meter)` would only make a total sum of those in the same rank. – LukStorms Jan 24 '19 at 11:09
@MikailBoybeyi A side note. I don't know which version of MySql you use. But know that in MySql 8 there are also [window functions](https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html). A gaps-and-islands problem like this one can sometimes be solved by using those. And then it wouldn't require variables. – LukStorms Jan 24 '19 at 11:31

Stored procedure is too slow in mysql

1 Answers1