Mysql – how to run limit query within condition in thesql

MySQL

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.

Best Answer

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

Related Question