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.