Could you tell me, will MySQL data in tables be physically fragmented if I insert new data and delete old data (insert in table top, and delete bottom rows)?
Will the size of table be growing in any way, before I do OPTIMIZE?
Are you DELETEing and INSERTing at about the same rate? Is the table InnoDB?
If Yes to both, then "don't worry". The DELETEs will free up blocks and the INSERTs will re-use those blocks (or new blocks if the INSERTs are getting ahead).
InnoDB tables are composed of 16KB blocks that are chained together in a BTree structure. What you described will be freeing blocks from one side of the tree and creating new blocks on the other side. However, each block can reside anywhere, so the concept of "side of the tree" is more 'virtual' than 'physical'.
After you have done a lot of churning, try the OPTIMIZE once. If it does not cut the table size in half, that is a further clue that OPTIMIZE is not worth it. A 30% shrinkage after a lot of churn is somewhat typical. But it stabilizes at about that, and won't go over about 30%.
If, on the other hand, you are DELETEing most of the table all at once, then you may be better off rebuilding the table with the not-to-be-deleted rows. This combines the delete and the optimize steps into one. Then use this to flip the tables 'instantly':
RENAME TABLE real TO old, new TO real; DROP TABLE old;
And do not do any inserts during the rebuild.