Optimized ways for defragment mysql database table We have a database in MySQL which has more than 500 GB of data, and currently in the process of purging data and count might go beyond 1 million. As we know that purging might lead to high fragmentation and will degrade performance. Also, under few circumstances where the purge data is more, it may make existing index unusable

In case if somebody has come across such scenario, please let me know best technique to handle the same
No, deletions do not significantly slow down what remains.

No, an index never becomes "unusable".

What are you purging?  "Old data"?  If so, then PARTITION BY RANGE(TO_DAYS(...)) is an excellent way to purge old data.  DROP PARTITION is 'instantaneous' and does cause any fragmentation.  My blog goes into the details of how to do the PARTITIONing, and the daily/weekly/whatever maintenance, including sample code.

If you cannot use partitioning in that way, you are stuck with whatever fragmentation happens; OPTIMIZE TABLE on a 500GB table would take a loooong time.  (There is an "online" version coming in 5.7, but perhaps not released yet.)

Are you INSERTing at the "end" of the table only?  Are you doing no UPDATEs or DELETEs other than the purging?  If yes to both, then there is a way to optimize the table or to copy the table to a partitioned table...

It involves walking through the table with the PRIMARY KEY and copying chunks of rows at a time.

To discuss further, please provide SHOW CREATE TABLE and answer the questions I posed.