At 09:06 AM 10/19/2006, you 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.
Of course like George mentioned, InnoDb is the best way to go. Other
alternatives is to use "Insert Delayed"
http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html provided all
your inserts are delayed (otherwise you could get corruption).
If you have optimized the table and there are no deleted rows, the Inserts
will not need to do any locking at all.
As for Updates, make sure you are using an index on the update, preferably
the primary key to identify the row, and update several rows in 1 sql
statement.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
