CODEWITHSUNDEEP
phpmysqlperformance
Andres
Andres

Reputation: 2023

Issues with selecting rows from large tables and updating them

I have a total of 7 tables. 1 of them will be used as comparison to the other 6 and it will get columns updated from the other 5. As far as columns there aren't that many, the main table has a total of 17 columns total. But rows, that main table has approx 66,000 rows. Now I have been doing a bit of research and set the tables to MYISAM instead of InnoDB but haven't seen much change in load speeds. So this is my initial approach:

  1. Select all rows from table 1 then in PHP do a foreach row get certain column values and do a specific select on each of the 6 other tables to return a single value:

    $all = $table1->selectAll();
foreach( $all as $a_template ){
      $id = $a_template['id'];

      $table2 = new Table2();
      $table3 = new Table3();
      $table4 = new Table4();
      $table5 = new Table5();
      $table6 = new Table6();

      $table2->select($a_template['column1']);
      $table3->select($a_template['column2']);
      $table4->select($a_template['column3']);
      $table5->select($a_template['column4']);
      $table6->selectByCode($a_template['npsecondarycode']);

      $table1 = new Table1();
      $table1->update($table2->getColumnVal(), $table3->getColumnVal(), $table4->getColumnVal(), $table5->getColumnVal(), $table6->getColumnVal(), $id);

    }

the function that gets called "getColumnVal()" all it does is return the value that was set when doing a select, no biggie there.

Now this process takes around 15 - 20 minutes going through 66,000 records and I haven't been able to speed it up any more and in some cases has cause memory issues to where I've had to upgrade the memory limit to 1GB atleast.

I did try going straight through MySQL and doing a select with inner joins, since I've heard that Databases can do more intense queries with larger data and better speeds but in this case it actually locks up and stops working all together, in some cases PhpMyAdmin gives me a 500 error.

I'm not sure which way to go from here since I've never come across this situation before. I would greatly appreciate it if anyone could point me to the right direction as to what steps should be taken to increase performance and speed in processing this data. TY :)

Update: here's the actual query based on the Rick James' answer:

UPDATE templates AS t
         JOIN equipos as e ON e.codigos  = t.npsegment1
         JOIN ubicaciones u ON u.attribute1  = t.atributo1
         JOIN tiendas ti ON ti.nombre_doc_stock = t.atributo1
         JOIN estados es on es.nporganizationid  = t.nporganizationid
         JOIN almacenes a on a.npsecondarycode  = t.npsecondarycode
         SET t.inclusion = e.inclusion,
             t.ubicacion = u.ubicacion11,
             t.tienda_id = ti.idi2b,
             t.estado = es.estado,
             t.considerar = a.considerar

The table Templates has 66K records

Upvotes: 0

Views: 58

Answers (1)

Rick James
Rick James

Reputation: 142296

Look up multi-table UPDATE. You might be able to perform the entire operation in a single SQL statement. It will run a lot faster. Something like

UPDATE table1
    JOIN table2 ON ...
    JOIN table3 ON ...
    ....
    SET table1.col1 = ...,
        table1.col2 = ...,
        ....;

Don't use MyISAM.

More

The query is copying stuff from several tables into one table (t)? If you are creating t from scratch, then it would probably be much faster to do INSERT INTO t SELECT ... JOIN ...

If you do need massive updates, and if the columns are independent, then it would probably work faster to do one table at a time. That is, 

UPDATE t JOIN e ON ... SET t.inclusion = e.inclusion;
UPDATE t JOIN u ON ... SET t.... = u....;
etc.

I suspect part of the problem is locking so many rows in so many tables.

Another approach is to "chunk" up the task. I discuss that here . It will take longer overall, but each chunk will finish in a civilized amount of time.

Upvotes: 2

Related Questions