Why is my database connection slower with MySQL than with Microsoft Access?

I am inserting large amounts of data into a MySQL database. There is no problem fetching data. However my insert (both INSERT and FASTINSERT) operations are very slow. Also, the same operations are much faster with a MS Access connection. Both databases reside on the same remote location and the data that is being accessed is of similar format.

Answer 1

[MathWorks Support Team]

MySQL tables are of type InnoDB by default. The table format InnoDB grows continually (on inserting data) and doesn't shrink when tables or data are deleted. It is recommended to use MyISAM as the table format, as this is both faster and more efficient.

The official documentation of MySQL says that InnoDB tables require a lot more disk space than MyISAM tables. Also, rollbacks take very long on InnoDB tables.

Please refer to the MySQL documentation at the following page for more information:

https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html