on 5/3/05 7:25 PM, Joseph Cochran at [EMAIL PROTECTED] wrote: > So here's my situation: we have a database that has a table of about 5 > million rows. To put a new row into the table, I do an INSERT ... > SELECT, pulling data from one row in the table to seed the data for > the new row. When there are no active connections to the DB other than > the one making the INSERT, it runs like a charm. But during normal > daytime operation, when we run around 50 connections (most sleeping at > any one time), it takes up to two minutes to do, and ends up locking > any other inserts or updates against that table for the entire time.
Since you are pulling data from only one row, you may be a prime candidate for <http://dev.mysql.com/doc/mysql/en/select-into-statement.html> which is as far as I can tell, more efficient as it stores the results as variables. I am really pretty new to all this, so just trying to help where I can, but you may find that selecting and inserting in the application logic level will perform better for you as well. I am not sure what goes on behind the scenes in a insert select, from what I have read, inserting a large amount of rows is going to get you faster results that selecting them by hand, but for one or few rows, it is not as optimum. > I'll get into more specifics if they're required, but I wanted to ask > in general if MySQL has tools to diagnose this, or if anyone has had > general situations like this. In SQL Server (which is where I have > most of my experience) I could use the trace tool and the Query > Analyzer to tell what the execution plan for the query was and thus > what's stalling it (an index gone bad, a weird locking situation, > etc). Take a look at: <http://dev.mysql.com/doc/mysql/en/slow-query-log.html> Though I have not had to use it yet, it may get you where you need to be. -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 <http://www.newgeo.com> Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
