You would need to convert the table to InnoDB as George mentioned.
Alternatively, break up your SELECT into many smaller selects. If your query is running slow now, it's only going to get slower and slower as your table grows. I've noticed with MySQL that large result queries don't slow down linearly. You will probably find that the many smaller queries actually shorten the entire time it takes.
As an example, I've got a process that merges a 6 million row table with a 300 million row table on a daily basis. It merges by selecting records within a 20 minute time period (there is a certain amount of comparisons involved in the merging). The tables have to be MyISAM because we are using MERGE tables. It used to take 9 hours with much smaller data sets. Breaking it up it now takes about 80 minutes with triple the amount of data. Each query only takes a few seconds, so locking isn't a big issue. Insert/Updates complete between the selects. I also don't have to worry mysql running out of memory and swapping to disk regardless of how big the dataset gets.
----- Original Message ----- From: "Jon Ribbens" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com> Sent: Thursday, October 19, 2006 10:06 AM Subject: How can I do a SELECT without locking the table against updates?
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
