Re: Slow speed in MySQL. Can't make faster :(

[Jay Pipes]

Couple points:

Is there a reason you are using a temporary table to store the records for updating? If it's absolutely necessary to use a temporary table then ensure that you have an index on *both* the temporary table and the parts table for the columns on which you wish to join.

Second, remember that MS Access is *not* the same as MySQL. Access is a desktop database. It is not a server application. MS Jet-SQL is an entirely different beast than MySQL and has it's own strengths and weaknesses. While it may perform faster for a few operations, it has serious drwabacks in terms of scalability and concurrency. MySQL, when indexed properly, will almost always perform better than Access. It's simply a matter of ensuring the queries are properly written and the schema is properly designed.

Do an output of an EXPLAIN SELECT for the INSERT statement in your second scenario and post output here...

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com