-1

My current table has users a, b, c, d, e, f registered.

Then I get a full list of all registered users and it lists c, d, e, f, g h.

This means a, b have unregistered, and g, h are new registering users.

Note*: There is no event that tells me when a users registers or unregisters. Only a list of all registered users, that I don't want to access more than once a day.

My current method of updating the MySQL:

UPDATE users SET registered = -1 WHERE registered = 1;

Then I proceed to update the table with my new list of all registered users:

INSERT INTO users (id, registered) VALUES($userid[i], 1) ON DUPLICATE KEY UPDATE registered = 1;

Then I proceed to update the table flagging users who have unregistered:

UPDATE users SET registered = 0, unregistered = 1 WHERE registered = -1;

Basically using -1 as indicator that a user was registered but not anymore according to the full list;

This works fine with a small table, but takes hours with tens of thousands.

Is there a better way?

Reducing it to a few minutes would be more than acceptable.

user1318504
  • 608
  • 1
  • 6
  • 11
  • 1
    Why are you doing all this, surely it should all be part of a user registering, or unregistering – RiggsFolly Jan 14 '19 at 09:30
  • I think you are too quick to down vote the question. The project is of the web scraping sort. There is no event that tells me when a users registers or un-registers. Only a list of all registered users, that I don't want to access more than once a day. – user1318504 Jan 14 '19 at 09:53
  • Actually I did not downvote the question :) – RiggsFolly Jan 14 '19 at 09:58
  • Ah, maybe the key part of the 'algorithm' is "not more than once a day"? – Rick James Jan 14 '19 at 16:33

2 Answers2

1

you can use where in like so

UPDATE users set registered = 1 WHERE id in (implode($userid,','))

you implode the users ids

the sql statement should look like this

UPDATE users set registered = 1 WHERE id in (1,2,3,5)

Edit: here is a link to mysql batch insert How to do a batch insert in MySQL

Mohammad Waleed
  • 195
  • 9
  • Thanks for the reply. I'm assuming this should be done in multiple queries with say 200 ids at a time? How many ids do you recommend per query, since the full registration list can be 100,000 – user1318504 Jan 14 '19 at 09:55
  • 1
    technically it's not limited you can do it for more elaborate answers check this link, https://stackoverflow.com/questions/4275640/mysql-in-condition-limit and this one https://stackoverflow.com/questions/1532366/mysql-number-of-items-within-in-clause – Mohammad Waleed Jan 14 '19 at 09:59
  • Is this going to speed up the processing? – RiggsFolly Jan 14 '19 at 10:09
  • I've updated the question. The update process also includes insertion of users not listed in the table. UPDATE WHERE IN would fail to add these users. – user1318504 Jan 14 '19 at 10:13
  • well if you loop and run the update for each user in php it would be slow as hell, a lot slower than doing it in a query in the database, in general do database stuff in database – Mohammad Waleed Jan 14 '19 at 10:14
  • this might help https://stackoverflow.com/questions/5526917/how-to-do-a-batch-insert-in-mysql – Mohammad Waleed Jan 14 '19 at 10:23
1

The secret is to throw out the nasty UPDATE.

Instead of registered column being some sort of flag, make it a DATETIME.

Now the test is on WHERE ts < NOW() - INTERVAL 1 DAY -- There is no need to change registered daily!

When you act on one of the items, simply update it with UPDATE ... ts = NOW() WHERE id = ....

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • ah, using a last_date_seen_registered. anyone who hasnt been on a recent list is known to have unregistered. yes! – user1318504 Jan 20 '19 at 11:35