Re: Utilize maximum system resources for better performance

[Rick James]

> LIMIT 2250000 , 50000

That's why it slows down. That particular chunk has to read 2250000 + 50000 rows.

For chunking, remember where you left off, then continue from there:

WHERE record_num > $left_off ORDER BY record_num LIMIT 50000

For more discussion of the technique, see:
http://mysql.rjweb.org/doc.php/pagination

> whats the default order by when i dont specify one ?

Whatever MySQL feels like. (Don't leave out ORDER BY if you have LIMIT.)

With InnoDB, you are better off chunking on the PRIMARY KEY(record_num), not a secondary key (enabled, record_num).

Plan A: Simply use blocks of 50000 record_nums, but let the WHERE clause toss out any that have enabled != 1. Hence, the chunks will be variable in size, possibly even 0.

Plan B:
SELECT record_num FROM tbl WHERE enabled=1 AND record_num > $left_off LIMIT 50000, 1;
Then use the result ($next) in:
SELECT * FROM tbl WHERE enabled=1 AND record_num > $left_off AND record_num <= $next;
That will give you exactly 50K rows (except the last time).

(I prefer Plan A, but for no really good reason.)

Did you specify "ROW_FORMAT=FIXED"? If so, why?

> so how can i make sure that recently inserted rows gets in the SELECT query ?

That requires some coordination between your processes. But, does it really matter? You will get the rows up to some point. I assume you are only INSERTing new rows, and they get new AUTO_INCREMENT record_nums, correct? If you are UPDATEing or DELETEing rows, all bets are off.

> how does mysql keeps track of whats already been included in SELECT and what to include in next SELECT ?

It does _not_ keep track.

Back to the updating and the "parallel table" approach.
Your rows average 1366 bytes wide. If so, then whenever you update a row, it has to shovel all 1366 bytes around to do the update. If, instead, you had a narrow parallel table of, say, 68 bytes, it could be updated with one-twentieth the effort.

When you are fetching in chunks, what do you do with the results? Are you really fetching "record_num, title"?