Deleting rows in joined tables quickly by substring comparison

I have two MyISAM tables, both of which, among other columns, contain a CHAR column. Table A contains the longer row, table B the shorter.

So what I need to do now is to delete all the rows from A where column A has ANY of the strings from table B’s column B as a substring. I.e. I only need to check for the first occurrence of any of column B’s strings in any row in table A. I’ve managed to do that already with "delete a.* FROM tblA a join tblB b where a.colA like binary concat(‘%’,b.colB,‘%’) ", but it is much too slow and I’m looking for how to make a query with the explained end result but much faster.

Both columns in the example use ASCII as their collation, and column A in table A contains a full text index, while column B in table B contains a regular index. Table B always contains 70 rows, and while the row count of table A will vary, in the example I’m testing my queries on, it contains 50 million rows.

However, I’ve come across another way of doing what I want, which is to filter out the rows I don’t want deleted, insert them into a new table and then drop the old table. That might be much faster, given that, in my example, most of the rows should be deleted, but I don’t know how to put together a query like that.

This insert query would then have to only insert those rows from table A where none, not even one of column B in table B, is a substring in a row of column A. And I don’t really have an idea how to put together a query for that.

Sounds to me like you have a database design problem. Tell us about the real problem you are trying to solve with your application rather than how you are trying to solve it. What is the high level overview of what you have going on.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.