Something no one mentions and I find important to stress is that Access has a file locking mechanism for sharing the database on a network share which is orders of magnitude more efficient than any network database out there and therefore MySQL is no different.
The reason is the following, a file locking mechanism permits all clients of an Access database to cache somehow the database file and therefore to execute queries on it as if it were a local file. This of course means very little data is sent over the network in the case for instance of a SELECT which returns 5000 records, because the select is actually returned from the cached file.
A traditional client server model such as Mysql instead means that a query for 5000 records will involve a lot of TCP packets between server and clients to return the whole 5000 records from server to client, that will involve a high latency between the query and the completion of the return of results.
A file locking mechanism of course has its limits which usually incur when more than 5 users share a database heavily with a lot of modifications to it, in that case the locking mechanism (which in Access can be very selective up to row locking) will cause more delay than say a client server database which could theorically allow a lot more of concurrency.
I came therefore to the conclusion that if you need to share a database on a LAN with a couple of users therefore Access has few competitors, if instead you need to host a database on the internet or with tens/hundreds of users of course Mysql is better.
I also notice many other databases tried implementing the file locking mechanism but most failed in stability or weren't able to make it work as efficiently as Microsoft with Access which must have spent a good load of time and money on it. SQLite and H2 database locking mechanisms for instance are either very low performance or are highly not recommended for risks of corruption of the database:
SQLite uses POSIX advisory locks to implement locking on Unix. On Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite assumes that these system calls all work as advertised. If that is not the case, then database corruption can result.
This database file locking mechanism uses native file system lock on the database file. No *.lock.db file is created in this case, and no background thread is started. This mechanism may not work on all systems as expected. [...] This feature is relatively new. When using it for production, please ensure your system does in fact lock files as expected.
Can anyone shed some light on how or why is Access so efficient and whether there are any serious competing products which follow the same file locking shared on a network approach and which implement it in an efficient way?