Hi Every one,

This is my scenario:

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?

TABLE 1
--------------
id
name
status
action_date
idZone
company_id

TEMP TABLE
--------------
id
status
action_date

i need to update STATUS and ACTION_DATE using ID to link the records.

any ideas?

Thanks for your help.

indexes ????

ID is the primary key that i use to match rows from both tables.

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.

Perhaps if you show us the full table definition including the indexes. Then show us the actual update statement you use.

THIS IS THE TABLE TO UPDATE


CREATE TABLE `prueba` (
`idRemision` VARCHAR(8) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`Nombre` VARCHAR(50) COLLATE latin1_swedish_ci DEFAULT NULL,
`Consecutivo` MEDIUMINT(9) NOT NULL DEFAULT '0',
`idZona` VARCHAR(7) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`Estado` ENUM('Pendiente','Distribuido','Rezago') NOT NULL DEFAULT 'Pendiente',
`id` VARCHAR(20) COLLATE latin1_swedish_ci DEFAULT NULL,
`causaRezago` TINYINT(3) UNSIGNED DEFAULT NULL,
`horayfecha` DATETIME DEFAULT NULL,
PRIMARY KEY (`idRemision`, `Consecutivo`),
KEY `Nombre` (`Nombre`),
KEY `IDCITI` (`id`),
KEY `Pruebas` (`idRemision`, `Consecutivo`),
CONSTRAINT `0_93` FOREIGN KEY (`idRemision`) REFERENCES `remision` (`idRemision`) ON DELETE CASCADE ON UPDATE NO ACTION
)ENGINE=InnoDB
CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

==================================================

THIS IS THE TEMPORARY TABLE USED TO BULK UPDATE

CREATE TABLE `descarga_ruta` (
`idCartero` TINYINT(3) UNSIGNED NOT NULL,
`idRemision` SMALLINT(5) UNSIGNED NOT NULL,
`consecutivo` SMALLINT(5) UNSIGNED NOT NULL,
`estado` VARCHAR(15) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`causaRezago` TINYINT(3) UNSIGNED DEFAULT NULL,
`horayfecha` DATETIME NOT NULL
)ENGINE=MyISAM
CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';


==================================================
BULK UPDATE STATEMENT

UPDATE prueba p, descarga_ruta dr SET p.estado = dr.estado, p.causaRezago = dr.causaRezago, p.horayfecha = dr.horayfecha WHERE p.idRemision = dr.idRemision AND p.consecutivo = dr.consecutivo


...as i said before, prueba has about 3 millions of records, i need to execute about 18,000 updates per day....


Hope this explains the scenario a bit more, thanks again for your help

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.

Mike

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.

Wow!, i cant believe i didn't notice that... thanks a lot guys, ill fix it... give it another try and let you know the results.

I never thaught that datatype conversion on a query would be that unefficient... Thanks everyone for you're help, that solve the issue.

Now it's very very fast.