I have a MySQL table of unique users. I have an 'active' boolean column (0 or 1) to denote whether the user is currently subscribed to the site. The primary table will hold the 'active' boolean column, and it might contain as many as ~30,000 IDs (active and not). I plan to populate that table via an array of active users.
Right now, I'm dumping all of the active IDs into a temporary table (T2) from the active-user array. From that table, I'm populating the primary table (T1) with any IDs that don't already exist.
INSERT INTO T1 (id_hashed, active_boolean, history, date)
SELECT id_hashed, '1', '6-29-18 on :: history', '$time'
FROM T2 t2
WHERE NOT EXISTS(SELECT 1 from T1 t1 where t2.id_hashed = t1.id_hashed) ";
Using two further queries, I'm also updating the 'active' boolean column in the primary table based on the temporary table of active users.
UPDATE T1
SET active_boolean = 0, history = concat('" . date("n-j-Y",time()) . " off :: ', history)
WHERE active_boolean = 1 AND id_hashed
NOT IN (SELECT id_hashed from T2 )";
UPDATE T1
SET active_boolean = 1, history = concat('" . date("n-j-Y",time()) . " on :: ', history)
WHERE active_boolean = 0 AND id_hashed
IN (SELECT id_hashed from T2 )";
I'm trying to make sure this is scalable, so I'm populating the temporary table (T2) with ~6,000 random IDs and the primary table (T1) with ~12,000 random IDs. Updates to the primary table are taking ~90 seconds with that much data. I'm a bit of a DB noob, so I'm sure there's a more effective way to do this. Thanks for any suggestions.
ETA: Explain Plan for insert into T1
id | 1
select_type | PRIMARY
table | t1
type | ALL
possible_keys |
key |
key_len |
ref |
rows | 3003
Extra | Using where; Using temporary
id | 2
select_type | DEPENDENT SUBQUERY
table | t2
type | ALL
possible_keys |
key |
key_len |
ref |
rows | 3003
Extra | Using where
Best Answer
Don't use
EXISTS
orIN ( SELECT ... )
.Use a multi-table
UPDATE
. Twice.Once with a
JOIN
to set the active rows.Once with a
LEFT JOIN ... IS NULL
to set the inactive rows.Or...
Once to set all users to inactive.
Once with a
JOIN
to set the active rows.