CODEWITHSUNDEEP
mysql
user1872325
user1872325

Reputation: 71

Optimizing mysql update on table over million records

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.

Upvotes: 1

Views: 878

Answers (2)

user1872325
user1872325

Reputation: 71

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.

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Best way to do would probably be joining both tables table1 and table2 like below. Though I am not sure from where the columns A1 and B1 coming into picture. Below sample code may not be exact per your table structure; you may have to change the column name(s) a bit per actual table definition.

UPDATE table1 a 
    JOIN table2 b ON a.A = b.A 
    AND a.B = b.B
    AND b.A LIKE '%A1' AND b.B LIKE '%B1' 
SET a.C = b.C

Upvotes: 0

Related Questions