Search code examples
mysqlinnodbmyisam

MySQL Cost: InnoDB on Cascade or MyISAM separate deletes

I'm curious as to which would be more cost efficient in terms of resources used by the server: On Cascade Delete that traverses multiple tables or multiple delete statements with the MyISAM engine?

Solution

  • On Cascade Delete have multiple advantages:

    • it can use transaction, so if one delete fails, all will fail and data will remain consistent
    • InnoDB uses row-level locks. To delete a record from MyISAM, entire table have to be locked. If there are a lot of concurrent queries, InnoDB will be faster.
    • As @Itay Moav said, with on cascade delete you will perform only one query, while with multiple deletes - multiple queries.