Practice has proved that the use of cursors in the database is better _ MySQL

Practice has proved that the use of cursors in a database is better. There are two tables in the database, one of which is huge, with about 10 million records (Big) and the other with only 5000 records (small ).

Currently, the table Big needs to be updated based on the table small.

UPDATE Big SET Bf1 = 0 WHERE BigId IN (SELECT BigId FROM Small WHERE Sf1> 0)

However, the execution speed is very slow, and the applications connecting to and using this DB server are also slow and almost unavailable.

Carefully analyze this SQL statement and use IN. Some people say that IN will be split into an OR expression, and the OR statement will not use an index, so sometimes OR will be replaced by union all.

However, union all cannot be used here.

According to the author's conjecture, this SQL statement will probably be searched by the entire table. if there are 10 million records in a large table, it will be at least 10 million times. how can this problem be solved.

So I chose to use a cursor instead. I first extracted the records of the small table and executed them in combination with the large table.

DECLARE curT cursor for select BigId FROM Small WHERE Sf1> 0; DECLARE @ Id INT; OPEN curT; fetch next from curT INTO @ Id; WHILE @ FETCH_STATUS = 0 begin update Big SET Bf1 = 0 WHERE BigId = @ BigId; fetch next from curT INTO @ Id; end close curT; DEALLOCATE curT;

In this way, the maximum number of records in the cursor is 5000, and the BidId has an index in the large table, which is divided into 5000 executions. although it takes some time to compile the connection, however, compared with the massive table-wide search, it is very small.

Practice has proved that the preceding statement is executed in about four minutes, during which the applications using this database are not affected and the speed is also fast.