On Thursday 19 October 2006 17:06, Jon Ribbens wrote: > 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.
Convert the table to InnoDB, which uses row level locking, as opposed to MyISAM which uses table level locking. -- George-Cristian Bîrzan Network Engineer _______________________________________ RCS & RDS Constanta Tel.: +40341.400.401 / +40341.400.402 Fax: +40341.400.450 http://www.rcs-rds.ro _______________________________________ Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
