How to use SQL in the most efficient way with very big tables


Problem :

I have a table purchase something like (id, buyer_id, seller_id, amount) with 20-30 millions of records Also i have a table with the same structure purchase_archive and table users_balance (id, user_id, balance)

I should write a script that:

  1. Moves records from purchase to purchase_archive table
  2. For each moved row users_balance should be updated (user balance should be decreased for amount if he is a buyer, and increased for the same amount if he is a seller)

What is the best way to solve this task? (PHP + Mysql PDO)

My assumption is:

  1. Set table engine to InnoDB
  2. SELECT batch of 1000 rows from first table
  3. Begin transaction (thats the reason for the InnoDB)
  4. For each row

    4.1 Store id in array ($temp)

    4.2 Update balance with query like

    SELECT `amount` FROM `purchase` WHERE `id` = :tid LIMIT 1 INTO @amount;
    UPDATE `users_balance` SET `balance` = CASE 
    WHEN `user_id` = :seller_id THEN `balance` + @amount 
    WHEN `user_id` = :buyer_id THEN `balance` - @amount END 
    WHERE `user_id` IN (:buyer_id, :seller_id);
    
  5. Move rows into archive with the query like that:

        INSERT INTO `purchase_archive` SELECT * FROM `purchase` WHERE `id` IN (".$temp.");
        DELETE QUICK FROM `transactions` WHERE `id` IN (".$temp.");
    
  6. End transaction

And repeat 2-6 in cycle.

The longest operation is point 4.2, and i dont know how to perform it faster without variables

Is there any faster approaches?

P.S. Sorry for my terrible english.



Solution :

You can try something like this:

update user_balance b 
  inner join (
    select b.user_id, 
           sum(case when p.buyer_id = b.user_id then p.amount else 0 end) bought, 
           sum(case when p.seller_id = b.user_id then p.amount else 0 end) sold
    from purchase p 
      inner join user_balance b 
        on p.buyer_id = b.user_id 
          or p.seller_id = b.user_id 
    group by b.user_id) q 
    on b.user_id = q.user_id 
    set b.amount = b.amount + q.sold - q.bought;

And it should do everything in a single query. You can limit the range further in the inner query if you wish. SQL Fiddle seems to be down so I can't provide a live demo, but there is this:

mysql> select * from user_balance;
+---------+--------+
| user_id | amount |
+---------+--------+
|       1 |     50 |
|       2 |     50 |
|       3 |     50 |
|       4 |     50 |
+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from purchase;
+-------------+-----------+----------+--------+
| purchase_id | seller_id | buyer_id | amount |
+-------------+-----------+----------+--------+
|           1 |         1 |        2 |     10 |
|           2 |         3 |        4 |     20 |
|           3 |         4 |        2 |      5 |
|           4 |         1 |        4 |      7 |
|           5 |         3 |        1 |      9 |
+-------------+-----------+----------+--------+
5 rows in set (0.00 sec)

and after the query....

mysql> update user_balance b inner join (select b.user_id, sum(case when p.buyer_id = b.user_id then p.amount else 0 end) bought, sum(case when p.seller_id = b.user_id then p.amount else 0 end) sold from purchase p inner join user_balance b on p.buyer_id = b.user_id or p.seller_id = b.user_id group by b.user_id) q on b.user_id = q.user_id set b.amount = b.amount + q.sold - q.bought;
Query OK, 4 rows affected (0.07 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from user_balance;
+---------+--------+
| user_id | amount |
+---------+--------+
|       1 |     58 |
|       2 |     35 |
|       3 |     79 |
|       4 |     28 |
+---------+--------+
4 rows in set (0.00 sec)

Mysql Tutorials

Mysql Howto..

How to output this hierarchical Mysql Structure?

How to know from client side if table was updated

How to backup a MySQL database?

How to insert 2 post (submitted) values that begins with specific names to same table row, and loop for more same results?

How to find duplicate rows with similar part of string

How to retrieve all remote mysql binary log files when you don't know the first filename (due to rotation on remote server)

How to select rows with specific condition within specific ranage in MySQL?

How can I cleanly output current mysql variables to a my.cnf?

How to efficiently check if record exists in mysql table?

how to create mysql database from sql query

How to properly apply indexex to my mysql DB

How to perform either update query if data is present else insert the data in Mysql?

How to filter record set with unknown column in MySQL [closed]

How to convert date in Text box to MySQL DATETIME format

How to use Check box to change MySql query in real time?

PHP / MYSQL returns array how to do a foreach

How to retrieve foreign key and INSERT INTO table (MySQL/InnoDB)

How to use %U in str_to_date for MySQL

How can I get images from the database to be output into a gallery slider?

How would I output this query in php?

How to turn on/off MySQL strict mode in localhost (xampp)?

SQL: Can't understand how to select from my tables

How can I call a mysql function (like mysql_insert_id) from Perl if i'm using DBI/DBD? [duplicate]

How to generate 1000000 rows with random data?

How to match date and time together in mysql/php

How to go from php MySQL sql injection vulnerable query to a MySQLi not vulnerable query

How to make newsfeed using relational tables in MySQL and PHP

How to find all siblings and half-siblings

Table exists in PHPmyAdmin but does not appear using SHOW TABLES query in PHP

How can i benchmark my SQL and PHP code?