List:General Discussion« Previous MessageNext Message »
From:Jon Ribbens Date:October 19 2006 2:06pm
Subject:How can I do a SELECT without locking the table against updates?
View as plain text  
I have a simple single-table SELECT query that takes of several
minutes to complete, due to a very large number of result rows being
involed. I don't think there is any way to optimise the query - MySQL
is already using the appropriate index etc, it's just a huge table and
the query has a large result set.

While the SELECT is executing, any INSERT/UPDATE queries on the table
are blocked.

Is there any way I can tell MySQL *not* to lock the table while the
SELECT is executing? I know this could lead to ambiguities, but in
this application it doesn't matter - if, for example, a new matching
row is added after the SELECT starts but before it finishes, it simply
doesn't matter whether that row is returned in the results or not.

If there is no way to do this in MySQL, does anyone have any
innovative suggestions as to how I could work around the problem? The
table is very large, so solutions involving multiple copies are tricky
to implement.
Thread
How can I do a SELECT without locking the table against updates?Jon Ribbens19 Oct
  • Re: How can I do a SELECT without locking the table against updates?George-Cristian BĂ®rzan19 Oct
  • Re: How can I do a SELECT without locking the table againstupdates?mos19 Oct
  • Re: How can I do a SELECT without locking the table against updates?Brent Baisley19 Oct
    • Re: How can I do a SELECT without locking the table against updates?Unknown Sender19 Oct