MySQL Database

If your site is busy, you may require the use of a database server, giving you genuine multi-user connectivity. In this case we can provide you with a MySQL database of an appropriate size.

Access is fine for most low volume sites. If the site has a lot of concurrent users then a database server is a better option. Access can only handle around 32 concurrent users before problems start occurring. This is because of the way the database is accessed. In reality Access can only be accessed by one user at a time - however this is disguised with low numbers of concurrent users, due to the speed with which the server handles the requests. A database server is optimised to handle larger amounts of data with true concurrent access. MySQL is an open source equivalent to MSSQL.

To cope with the fast pace of evolution in database technology, an increasing number of programmers are deliberately switching from desktop database applications, such as Microsoft Access and SQL Server, to MySQL. Although MySQL isn’t serious competition for SQL Server, many service providers now support MySQL as an inexpensive and effective alternative.

For simultaneous user support, there is no comparison between Access and MySQL. Access begins to short out at about 15 simultaneous connections, and we've heard complaints when the number is as low as 5. This is not to suggest that only 5 users can connect to an Access-supported site at the same time. Simultaneous connection actually refers to concurrent processes. As such, Access can actually handle unlimited connections, as long as those connections remain under the process limit. Read-only sites (which really aren't as rare as you might think) can support up to 255 users. Larger sites do inevitably upgrade to SQL Server to improve performance and stability.

In contrast, MySQL's maximum connection default is 100 users. And while one should never base performance evaluations on a program's default settings, we haven't heard any complaints from users relying on MySQL for large sites with heavy connection play. In addition, plenty of traffic doesn't seem to impact query optimisation significantly.

In tests with equivalent hardware and file sizes on a Windows 98 system, MySQL consistently performs faster than Access 2000--but not always. Updates show the largest discrepancy in the area of performance, with Access often requiring twice as long to complete the same task. When you're dealing with small amounts of data and fast systems, this discrepancy isn't noticeable. It becomes a problem only when handling hundreds of thousands of records. MySQL loses to Access only when dealing with object structure rather than data. When creating a table and an index, MySQL locks out the table, which slows things down when working with large amounts of data. This latter issue isn't really a typical concern in Web programming, though, where hits and querying matter more than data storage. In this area, MySQL wins.

Some of the information above has been drawn from builder.cnet.com.