Re: InnoDB Table Locking Issue

[Scott Switzer]

Thanks for the message.  I should rephrase - the data set is millions of 
rows, but the tables are indexed, and an EXPLAIN looks like it is using 
indexes effectively.  The query produces the exact same results both 
times (with and without LOCKing).  Is there a reason that by calling the 
query via PHP (v4.3) it would either not LOCK the tables effectively, or 
optimise the query differently than when running in the MySQL client?

If you are duly motivated, I could send you the exact query and a test 
DB dump...

Thanks,
Scott


Marc Slemko wrote:

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]