0

I have two tables

table_a_mod (2 million rows)
table_a (2 million rows + new live data)

table_a_mod is identical to table table_a except for two extra columns

table_a is a "live" table with data being written to it semi-regularly.

To keep table_a_mod up to date I would like to copy data from table_a to table_a_mod

By using a regular join, I am able to select the data:

SELECT *
FROM table_a t1
LEFT JOIN table_a_mod t2
USING(id)
WHERE t2.id IS NULL

However, this query takes about 1 minute to run.

Is there a more efficient query along the lines of:

SELECT  *
FROM table_a t1
LEFT JOIN 
(
  SELECT id 
  FROM table_a_mod
  ORDER BY id DESC
  LIMIT 1
) t2
USING (id)
WHERE t1.id > t2.id

ie. Get the largest id from table t2. Select all rows from table t1 with id greater than that number.

I've tried different permutations of the above query but I'm falling short.

Edit 1

In response to @danblack

EXPLAIN {slow_query}

"1" "SIMPLE"    "table_a"   "ALL"   \N  \N  \N  \N  "2353123"   ""
"1" "SIMPLE"    "table_a_mod"   "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "db.table_a.id" "1" "Using where; Not exists"

I'm assuming id is a primary key in both

Yes. id is a primary key in both.

Are the tables innodb and is there sufficient innodb_buffer_pool_size to hold both tables?

Both tables are innodb. And innodb_buffer_pool_size is 8388608

Is "new live data" always of an id above the previous table?

Yes, because it's auto incrementing

Why are you keeping two nearly identical tables

It is a temporary situation. We will drop the older one and use the newer one.

2
  • There is limits how efficient comparing 2 million rows from two tables can be. If you change SELECT * to SELECT t1.id, t2.id is that quicker? What does EXPLAIN {query} show? I'm assuming id is a primary key in both? Are the tables innodb and is there sufficient innodb_buffer_pool_size to hold both tables? Is "new live data" always of an id above the previous table? Why are you keeping two nearly identical tables, perhaps the performance and be improved by answering that.
    – danblack
    Jan 21, 2019 at 4:36
  • @danblack Thanks for your comment. I've added the clarifications in the question Jan 21, 2019 at 5:27

1 Answer 1

3

How much RAM do you have? If you have more than 4GB, set innodb_buffer_pool_size to 70% of available RAM. This is the most important thing to tune. With it set to only 8M, you are definitely I/O-bound. Increasing the size will probably shrink the time to under 10 seconds.

Your second idea should be good (assuming the only new rows have higher ids), but let's get rid of the LEFT and use MAX:

SELECT  *
    FROM  table_a t1
    JOIN  
        ( SELECT  MAX(id) AS max_id
            FROM  table_a_mod ) t2
    WHERE  t1.id > t2.max_id 
5
  • Thank you both (@Rick James and @Akina). I've tweaked it a little and it works perfectly. SELECT * FROM table_a t1 JOIN ( SELECT MAX(id) AS id FROM table_a_mod ) t2 WHERE t1.id > t2.id Jan 21, 2019 at 7:07
  • In the interest of accuracy, I will mark it as correct once the SQL has been edited correctly. Jan 21, 2019 at 7:08
  • 1
    " How much RAM do you have? If you have more than 4GB, set innodb_buffer_pool_size to 70% of available RAM. This is the most important thing to tune. " @denormalizer Assuming the physical server is dedicated to run MySQL only.. Jan 21, 2019 at 16:16
  • @Akina - Thanks. I changed it in a different way. (An example of "overloading" a name can cause trouble.)
    – Rick James
    Jan 21, 2019 at 17:35
  • @Akina - Rats, I did not simplify it enough!
    – Rick James
    Jan 21, 2019 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.