Take the 2-minute tour ×

I have a table with columns like (PROPERTY_ID, GPSTIME, STATION_ID, PROPERTY_TYPE, VALUE) where PROPERTY_ID is primary key and STATION_ID is foreign key.

This table records state changes; each row represents property value of some station at given time. However, its data was converted from old table where each property was a column (like (STATION_ID, GPSTIME, PROPERTY1, PROPERTY2, PROPERTY3, ...)). Because usually only one property changed at time I have lots of duplicates.

I need to remove all successive rows with same values.

Example. Old table contained values like

time  stn   prop1  prop2
100   7     red    large
101   7     red    small
102   7     blue   small
103   7     red    small

The converted table is

(order by time,type)          (order by type,time)
time  stn type  value         time  stn type value
100   7   1     red           100   7   1    red
100   7   2     large         101   7   1    red
101   7   1     red           102   7   1    blue
101   7   2     small         103   7   1    red
102   7   1     blue          100   7   2    large
102   7   2     small         101   7   2    small
103   7   1     red           102   7   2    small
103   7   2     small         103   7   2    small

should be changed to

time  stn type  value
100   7   1     red
100   7   2     large
101   7   2     small
102   7   1     blue
103   7   1     red

The table contains about 22 mln rows.

My current approach is to use procedure to iterate over the table and remove duplicates:

    DECLARE psid,nsid INT DEFAULT null;
    DECLARE ptype,ntype INT DEFAULT null;
    DECLARE pvalue,nvalue VARCHAR(50) DEFAULT null;
        SELECT station_property_id,station_id,property_type,value 
        FROM station_property 
        ORDER BY station_id,property_type,gpstime;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO id,nsid,ntype,nvalue;
        IF done THEN 
            LEAVE read_loop;
        END IF;        
        IF (psid = nsid and ptype = ntype and pvalue = nvalue) THEN
            delete from station_property where station_property_id=id;
        END IF;
        SET psid = nsid;
        SET ptype = ntype;
        SET pvalue = nvalue;
    CLOSE cur;

However, it is too slow. On test table with 20000 rows it removes 10000 duplicates for 6 minutes. Is there a way to optimize the procedure?

P.S. I still have my old table intact, so maybe it is better to try and convert it without the duplicates rather than dealing with duplicates after conversion.


To clarify which duplicates I want to allow and which not.

  1. If a property changes, then changes back, I want all 3 records to be saved, even though first and the last contains same station_id, type, and value.
  2. If there are several successive (by GPSTIME) records with same station_id, type, and value, I want only the first one (which represents the change to that value) to be saved.

In short, a -> b -> b -> a -> a should be optimized to a -> b -> a.


As @Kickstart suggested, I've created new table, populated with filtered data. To refer previous rows, I've used approach similar to one used in this question.

rename table station_property to station_property_old;
create table station_property like station_property_old;

set @lastsid=-1;
set @lasttype=-1;
set @lastvalue='';

INSERT INTO station_property(station_id,gpstime,property_type,value)
select newsid as station_id,gpstime,newtype as type,newvalue as value from
-- this subquery adds columns with previous values
    (select station_property_id,gpstime,@lastsid as lastsid,@lastsid:=station_id as newsid,
    @lasttype as lasttype,@lasttype:=property_type as newtype,
    @lastvalue as lastvalue,@lastvalue:=value as newvalue
    from station_property_old
    order by newsid,newtype,gpstime) sub
-- we filter the data, removing unnecessary duplicates
where lastvalue != newvalue or lastsid != newsid or lasttype != newtype;

drop table station_property_old;
share|improve this question
Why is the 103 7 1 red in the resulting (de-duplicated) dataset? –  Branko Dimitrijevic Nov 22 '12 at 12:57
@BrankoDimitrijevic I've added data to example. At time=102 prop1 changed from "red" to "blue". At time=103 prop1 changed back. I want both changes to be reflected in the table. What I don't want to have is records that do not indicate changes -- such as 103 7 2 small that was added when I've "exploded" the large table. –  aimozg Nov 22 '12 at 13:07
@BrankoDimitrijevic see the table ordered by (type,time). It is more clear what rows should be deleted. –  aimozg Nov 22 '12 at 13:11
add comment

2 Answers

up vote 1 down vote accepted

Possibly create a new table, populated with a select from the existing table using a GROUP BY. Something like this (not tested so excuse any typos):-

INSERT INTO station_property_new
SELECT station_property_id, station_id, property_type, value 
FROM (SELECT station_property_id, station_id, property_type, value, COUNT(*) FROM station_property GROUP BY station_property_id, station_id, property_type, value) Sub1
share|improve this answer
You see, I don't need just unique station/type/value combinations. If a property changes, then changes back, such solution will store only one record of first value. I'll update the example to reflect this requirement. –  aimozg Nov 22 '12 at 12:50
I've constructed a query selecting duplicates: select * from station_property p1 where p1.value = (select value from hive.station_property p2 where p2.property_type = p1.property_type and p2.gpstime>p1.gpstime order by p2.gpstime limit 1);, but it is slower than the procedure. –  aimozg Nov 22 '12 at 12:53
Do I take it that you want the unique time, stn, type, value but with the latest gpstime? –  Kickstart Nov 22 '12 at 13:02
No. Please see the "update" paragraph I've added and re-ordered table in the example. –  aimozg Nov 22 '12 at 13:21
Afraid I can't think of a pure SQL way to do it. You could save a tiny amount putting the code to save the id, type and value into the IF statement. Might be quicker to add the ids to delete to a temp table, do your loop and then do a single delete statement joining your main table with the temp table (this might save MySQL trying to clean up keys, etc, between each of 10s of thousands of deletes). –  Kickstart Nov 22 '12 at 14:14
add comment

Regarding chainging properties, cant you put a unique constraint to ensure the combination of station/type/value columns is unique. That way you will not be able to change it to a value which will result in a duplication.

share|improve this answer
No, I cannot. Combination of station/type/value is not necessary unique. Please see "update" paragraph. –  aimozg Nov 22 '12 at 13:22
add comment

Your Answer


