1

I have a huge database around ~20,000,000, presently I am running a php script, which verifies if user info is 'valid or invalid'. There are several entries named 'invalid' in the database. Problem is how to make a limit on database to scan only valid entries. I am running google cloud to device messaging service which only allows push messages to 200,000 users per day. So, I want to make limit per query and automate via cron.

Presently query which is running on database is

for valid:

SELECT * FROM TNAME WHERE SETNO=1 & flag='valid' ORDER BY id;

for invalid:

SELECT * FROM TNAME WHERE SETNO=1 & flag='invalid' ORDER BY id;

Different numbers are assigned to setno corresponding to database rows.

5
  • 1
    What about splitting the table in two, and store valid and invalid rows separately? Jun 8, 2012 at 8:27
  • did you mean, all invalid and valid entries are stored in separate tables?
    – Shashank
    Jun 8, 2012 at 8:32
  • 1
    Yes. That way you wouldn't need the flag = 'valid' condition. Jun 8, 2012 at 8:42
  • So you have those two queries running, what issue are you having? Jun 8, 2012 at 13:42
  • $DTest:- I am trying to seeking a convenient way to automate my task..in this scenario user who aren't registered with me, flagged by invalid, who are flagged by valid. Next time when notification send only to valid users not to invalid one. But i don't know the id's of invalid one so i ran a query mentioned above flag='valid', but might be query waste processing on invalid id's to check weather it is valid or not.
    – Shashank
    Jun 8, 2012 at 14:10

1 Answer 1

1

From your question, let's assume your table is called TNAME

You should collect the ids that are valid

First create a table to hold IDs and a process status per ID

CREATE TABLE idlist
(
    id INT NOT NULL,
    processed INT NOT NULL DEFAULT 0,
    PRIMARY KEY (key),
    UNIQUE KEY processed (processed,key)
) ENGINE=MyISAM;

Next, preload load all new IDs

INSERT IGNORE INTO idlist (id) SELECT id FROM TNAME WHERE SETNO=1 AND flag='valid';

This will load ids that are valid. IDs that were already loaded are bypassed

Using this idlist, pickup the first 200,000 ids

CREATE TABLE idlist200K SELECT id FROM idlist WHERE processed=0 ORDER BY id LIMIT 200000;
ALTER TABLE idlist200K ADD PRIMARY KEY (id);

Process the 200K rows with an INNER JOIN with queries like this:

SELECT B.* FROM idlist200K A INNER JOIN TNAME B;

When done processing, mark the IDs as processed:

UPDATE idlist A INNER JOIN idlist200K B USING (id) SET A.processed = 1;
DROP TABLE idlist200K;

All you have to do from here is automate all these steps, except the initial table creation

INSERT IGNORE INTO idlist (id) SELECT id FROM TNAME WHERE SETNO=1 AND flag='valid';
CREATE TABLE idlist200K SELECT id FROM idlist WHERE processed=0 ORDER BY id LIMIT 200000;
ALTER TABLE idlist200K ADD PRIMARY KEY (id);
SELECT B.* FROM idlist200K A INNER JOIN TNAME B; -- Whatever processing
UPDATE idlist A INNER JOIN idlist200K B USING (id) SET A.processed = 1;
DROP TABLE idlist200K;

Give it a Try !!!

1
  • it seems better one!
    – Shashank
    Jun 9, 2012 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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