Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2010
    Posts
    5

    Question Unanswered: Mysql Bulk Update is extremely slow

    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.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    indexes ????

  3. #3
    Join Date
    Dec 2010
    Posts
    5
    ID is the primary key that i use to match rows from both tables.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Perhaps if you show us the full table definition including the indexes. Then show us the actual update statement you use.

  6. #6
    Join Date
    Dec 2010
    Posts
    5

    Question

    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

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Dec 2010
    Posts
    5

    Thumbs up

    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.

  10. #10
    Join Date
    Dec 2010
    Posts
    5

    Cool It Works!

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •