MySQL Forums
Forum List  »  Performance

Re: Utilize maximum system resources for better performance
Posted by: Rick James
Date: September 18, 2015 09:54PM

> 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"?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilize maximum system resources for better performance
1123
September 18, 2015 09:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.