MySQL vs. Access

[dbrooks74]

Could someone tell me if MySQL is fastern than Access? I would like to create a small databases and already know access but I would like to know if it would even be beneficial to me to know MySQL. Thanks. -db

 

[sleipnir214]

It's not a matter simply of speed. A recommendation of which to use depends on multiple issues. The speed of a database engine depends on multiple factors: whether you access the data natively or through an abstraction layer like ODBC; whether the server runs on a machine separate from the application or on the same machine; the number of users accessing the data at the same time; your available network bandwidth (if server and app are on separate machines); and other factors.


MySQL is a database server -- it's more comparable to Microsoft SQL Server than to Access, though MySQL's feature set is sparser than SQL Server's.

If you are writing a single-user, all-Microsoft-software application for the desktop, I really can't recommend MySQL over Access. The integration of Access into Microsoft's other software is better than MySQL's.

If however, you intend to write a multiuser application, leave Access behind, whether for MySQL or another database server. Access uses the Microsoft Jet database engine, and that engine must be installed locally on any system which intends to use data in an Access database. Multiple applications managing a single data source is a bad idea -- you run into all sorts of deadlocking and data integrity issues. MySQL, as well as other dedicated database servers, has a single engine maintaining the data, with that engine's being able to communicate to multiple users. One engine, fewer problems.

Also, if you intend to have the data accessed via the web, I recommend you leave Access behind. Even though all users are connecting to data from a local machine, the Jet database engine is not reentrant, which means another copy of the engine must be run for each user. If you have 5 users connecting to your Access-driven website, then Jet will be running 5 times on the server. You'll be much better off moving the data to a dedicated server.

If you are wishing to learn server-driven database technologies, MySQL is a good choice. You can't beat the price, it's easy to set up, it runs on multiple OSes, it has good performance, and it is well documented. ______________________________________________________________________
TANSTAAFL!

 

[dbrooks74]

Thanks for the reply. I have a better understanding of how I'm going to make my decision. Thanks. -db