Mysql – Update MySQL user table of active/inactive members based on array

MySQLupdate

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 or IN ( 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.