I have an innodb table on a mysql database server with almost 3 millions of records. Every morning i upload about 800 records from that table on a PDA.
At the end of the day i update my mysql table from those records with new values using 800 update queries.
This is extremely slow (20 minutes)...
I also tried inserting the rows from the PDA to a temporary table( wich is really fast ) and then update my MySQL table from this temporary table.
this update takes hours and locks the table until it ends.
How can i speed up this updates?
i need to update STATUS and ACTION_DATE using ID to link the records.
How many indexes do you have on the table being updated? I understand you have a primary key on ID but if you have lots of indexes, each update will require an update not only to the table data and the primary key but also to any additional indexes on the table.
Also have a look at the execution plan. If it is using the entries from the table being updated as the primary then your temporary table will require an index on the ID too.
At first glance you'd expect it to use the index on idRemision in both tables but sadly it won't because they appear to be entirely different types:
VARCHAR(8) COLLATE latin1_swedish_ci NOT NULL DEFAULT ''
SMALLINT(5) UNSIGNED NOT NULL
This means the optimiser is left with no suitable indexes and it will do a table scan. I'd suggest you change the type in the temporary table to the same type in the main table and have another go. It should a matter of seconds to run the update after this change.
If you look at your table definition your temporary table is using different data types than in your principle table. This will cause the query to perform data conversions and in some cases will cause it not to use the primary key index. I would change the data types of your temp table.