Optimizing mysql update on table over million records
Question: I have a mysql table which contains about 1.7 million records. The goal is to
fill missing information in the table. The following is the pseudocode of what
I am trying to do:

    
    
    SELECT DISTINCT A,B FROM table1
    

for each value A1,B1 from above query

    
    
    SELECT C FROM table2 WHERE A LIKE '%A1' AND B LIKE '%B1'
    UPDATE table1 SET C=C WHERE A=A1 AND B=B1
    

Unfortunately, the nature of the problem is that parts of values A1 and B1 are
in table2 columns, so I cannot use JOIN statements.

There are about 0.15 million unique updates which have to be made, and this
will affect the 1.7 million records.

I have built indexes on columns A1, B1 in table 1 and table 2 respectively.

I wrote a simply python script to do the above, but it is way too slow to
imagine running the script - it has run for about 15 hours now and only 1/4th
of the work has been done.

How do I optimize the queries in mysql. The tables use InnoDB.


Answer: After a bit of research and experiments, I found that indexes are not going to
be used for leading-wildcard queries (queries of type %value), so any effort
to optimize through improved querying was only futile.

Fortunately for me, I knew the most important variants that would fall under
the leading-wildcard queries (the ones that covered the most number of
records), and I directly searched for them, by avoiding the LIKE clause. After
most of the records were covered, I copied the rest of the records into
another table, and used the LIKE clause to achieve my goals.

Thanks to everyone who helped.

