1

Here is my query:

DELETE FROM my_table WHERE name = ANY (
SELECT name FROM (
    SELECT sd1.name
    FROM my_table sd1 JOIN my_table sd2
    ON sd2.name > sd1.name
    AND sd2.name & sd1.name = sd1.name
    AND sd1.number >= sd2.number) AS stmp
);

name is primary key BIGINT type.

It takes a long time to run even there are not so many rows in my_table. Any ideas on how to make it run faster.

Pol99
  • 111
  • 8

2 Answers2

1

You are looking for sd1.names where exists another record with certain criteria. Then you delete all records with the names found.

Isn't this simply: delete records for which exists another record with certain criteria?

DELETE FROM my_table
WHERE EXISTS
(
  SELECT *
  FROM
  (
    SELECT *
    FROM my_table sd2
    WHERE sd2.name > my_table.name
    AND sd2.name & my_table.name = my_table.name
    AND my_table.number >= sd2.number
  ) dummy
);

(UPDATED: MySQL cannot access the same table in UPDATE or DELETE and one must wrap the subquery so the table gets kind of hidden hidden one level deeper. Looks absolutely senseless, but is supposed to solve this issue. Hope it does.)

That's less complicated and therefore maybe faster. Especially since EXISTS has to find only one match per main record and can stop there, whereas a join looks for all matches.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Well, it gives me syntax error: mysql> DELETE FROM my_table sd1 WHERE EXISTS ( SELECT * FROM my_table sd2 WHERE sd2.name > sd1.name AND sd2.name & sd1.name = sd1.name AND sd1.number >= sd2.number ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sd1 WHERE EXISTS ( SELECT * FROM my_table sd2 WHERE sd2.name > sd1.name ' at line 1 – Pol99 Nov 17 '16 at 02:14
  • I had to google this. MySQL doesn't accept a table alias for whatever reason. (They do provide a queer syntax for an alias, but we can easily do without here.) Please see my updated answer. – Thorsten Kettner Nov 17 '16 at 11:01
  • I use MySQL aliases all the time. May be something is special in that case. When I run the query as you suggested it errored with message: ERROR 1093 (HY000): You can't specify target table 'my_table' for update in FROM clause – Pol99 Nov 17 '16 at 18:43
  • And here is more on that http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Pol99 Nov 17 '16 at 18:47
  • Ah another MySQL specialty. If I remember correctly you need to wrap this in a dummy query in MySQL. I've updated again. Hope this works for you. – Thorsten Kettner Nov 17 '16 at 23:34
  • DELETE FROM my_table WHERE EXISTS ( SELECT * FROM ( SELECT * FROM my_table ) sd WHERE sd.name > my_table.name AND sd.name & my_table.name = my_table.name AND my_table.number >= sd.number ); – Pol99 Nov 18 '16 at 01:14
0

Apparently you can pare DELETE with INNER JOIN http://www.mysqltutorial.org/mysql-delete-join/ It still takes a while if JOIN is large.

Pol99
  • 111
  • 8