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