· 7 years ago · Jan 13, 2019, 09:16 PM
1Optimized sql query to delete rows
2A B C D
31 2 0 2012-10-05 18:37:00
41 3 0 2012-10-05 20:37:00
51 4 1 2012-04-07 18:37:00
62 1 1 2012-10-05 18:12:40
72 2 0 2012-10-04 18:37:00
82 3 0 2011-10-05 12:37:00
9
10delete from t
11where not exists (select 1
12 from ((select A, B
13 from t
14 where C = 0
15 order by D desc
16 limit 10
17 ) union all
18 (select A, B
19 from t
20 where C = 1
21 order by D desc
22 limit 10
23 )
24 ) a
25 where a.A = t.A and a.B = t.B
26 )
27
28DELETE
29 ex
30FROM
31 ex
32INNER JOIN
33(
34 SELECT
35 C, MIN(D) D
36 FROM
37 (
38 (
39 SELECT
40 C, D
41 FROM
42 ex
43 WHERE
44 C = 0
45 ORDER BY
46 D DESC
47 LIMIT 10
48 ) UNION (
49 SELECT
50 C, D
51 FROM
52 ex
53 WHERE
54 C = 1
55 ORDER BY
56 D DESC
57 LIMIT 10
58 )
59 ) d1
60 GROUP BY
61 C
62 ORDER BY
63 C
64) d2 ON d2.C = ex.C
65WHERE
66 ex.D < d2.D
67
68SELECT
69 C,
70 COUNT(*),
71 MIN(D),
72 MAX(D)
73FROM
74 ex
75GROUP BY
76 C
77ORDER BY
78 C
79
80C cnt MIN(D) MAX(D)
810 10 10/5/2012 2:14:53 AM 10/5/2012 7:21:23 PM
821 10 10/2/2012 1:41:21 PM 10/5/2012 2:57:34 PM
83
84DROP TABLE IF EXISTS ex_old;
85DROP TABLE IF EXISTS ex_new;
86CREATE TABLE ex_new LIKE ex;
87
88INSERT INTO
89 ex_new
90SELECT
91 ex.*
92FROM
93 ex
94INNER JOIN
95(
96 SELECT
97 C, MIN(D) D
98 FROM
99 (
100 (
101 SELECT
102 C, D
103 FROM
104 ex
105 WHERE
106 C = 0
107 ORDER BY
108 D DESC
109 LIMIT 10
110 ) UNION (
111 SELECT
112 C, D
113 FROM
114 ex
115 WHERE
116 C = 1
117 ORDER BY
118 D DESC
119 LIMIT 10
120 )
121 ) d1
122 GROUP BY
123 C
124 ORDER BY
125 C
126) d2 ON d2.C = ex.C
127WHERE
128 ex.D >= d2.D;
129
130RENAME TABLE ex TO ex_old, ex_new TO ex;