2

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
  • 1
    What indexes do you have on the tables? And what does the explain plan look like? – sticky bit Jul 5 '18 at 21:28
  • I don't have indexes yet. When I test a lookup for one user on T1 (e.g. for a user login), it's fast enough, so I wasn't sure indexes would be worth it? I'm willing to sacrifice slow INSERTs/UPDATEs on my side of things as they will take place once a day at most. – bill Jul 5 '18 at 21:48
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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