On Tue, 27 Apr 2004, Scott Switzer wrote: > Hi, > > I am having a difficult time with a query. My environment is MySQL > v4.0.16 (InnoDB tables) running on Linux (latest 2.4 kernel). > Basically, I am running a query of the form: > > INSERT INTO temp_tbl > SELECT c1,c2... > FROM t1,t2,t3,t4 > WHERE ... > > It is a very complex query, which looks at millions of rows of data to > produce results. The issue is this: > > When running from the MySQL command line: > Normally, when the query is run, it takes about 5 minutes to complete. > When I LOCK t1,t2,t3,t4 before running, it takes 8 seconds to run.
Are you sure it is actually _working_ when it takes 8 seconds to run? You say it is a very complex query that looks at millions of rows ... unless those are all cached (and they could be, depending on your setup), 8 seconds would probably be too fast for it to run no matter how mysql optimized it. Triple check that if you start with an empty temp_tbl it actually inserts everything it should into temp_tbl. If you aren't locking temp_tbl, I wouldn't expect the query to actually work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
