MySql query without affecting other operations

Question

I have a huge (poorly designed) InnoDB table with millions of records. When I ask this guy, the execution lasts a few minutes. How can I guarantee that during this time no other operations (queries, inserts or updates) will be affected? The last thing I want is locks or timeouts for others while my request is being executed.

Here is the actual request.

SELECT html FROM cms_log where class_name ='main_pages' order by date_created desc; 

Currently, the class_name field is not indexed, but that is not what I can change at the moment.

This post suggested using

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

before running my request. But this is a rather old post, and it only talks about castles. Can someone confirm that this is the way to go or give the best way to run the most secure request? (I also don’t care about dirty and phantom readings, I just don’t want to influence other operations).

Answer 1

So it seems like this is the way to go.

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

Doing this before the request does not seem to guarantee that no other operations will get the scary "lock wait timeout" if this long request is executed.

Answer 2

I do not agree with the link you posted. Setting the isolation level for READ UNCOMMITTED should not make your selected queries faster / affect any other queries differently.

A pure choice like yours in InnoDB should not block anything (other than its own internal data structures).

In general, SELECT can block reading, writing, or being absent. depending on the context. In this context, when you have not explicitly requested a lock, and you are not updating anything, you should not use row locking.

It is suggested that you usually use the READ COMMITTED or REPEATABLE READ isolation level.