Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
What about splitting the table in two, and store valid and invalid rows separately? –  dezso Jun 8 '12 at 8:27
    
did you mean, all invalid and valid entries are stored in separate tables? –  shihon Jun 8 '12 at 8:32
1  
Yes. That way you wouldn't need the flag = 'valid' condition. –  dezso Jun 8 '12 at 8:42
    
So you have those two queries running, what issue are you having? –  Derek Downey Jun 8 '12 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. –  shihon Jun 8 '12 at 14:10
add comment

1 Answer 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 !!!

share|improve this answer
    
it seems better one! –  shihon Jun 9 '12 at 11:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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