Differences between MySQL and Access

To adapt to the ever-changing technologies, a considerable number of software engineers have gradually switched from desktop software such as Microsoft Access and SQL Server to MySQL. Although MySQL is not a rival to SQL Server, many service providers support MySQL and regard it as a cheap and efficient alternative.

Susan Sales Harkins often publishes articles on CNET Builder.com and is an expert proficient in Microsoft Office. She is also the author of Using Microsoft Access 97 and Using Microsoft Access 2000, both of which are published by Que.

Martin W. P. Reid also frequently published articles in CNET Builder.com, an analyst and programmer at Queen's University in Belfast. He also guides relational design courses and serves as a consultant for small businesses in Northern Ireland.

▲Reasons for using MySQL

If you are looking for reliable software to support your website development, the following reasons indicate why you should consider MySQL instead of others:

· It is cheap (usually free of charge ).

· It has less network load.

· It is well Optimized (Highly Optimized ).

· It is relatively simple for applications to back up data.

· It provides an elastic Extended Interface (ODBC) for different data formats ).

· It is easy to learn and easy to operate.

· You can afford the customer support fee.

▲Questions about "$"

Simply put, you won't find anything cheaper than MySQL. In fact, MySQL is free for most users. Sometimes a small licensing fee is required, but the payment is limited to the following two situations:

· Use MySQL in embedded Mode

· Only use commercial application software of MySQL

For example, for MySQL of Windows, authorization is required. Even though I only pay a little more than $200, MySQL is much cheaper than any other software. The retail price of Office XP Developer is USD $799, And the upgrade price is USD $549. The price of Access 2002 is USD $339, And the upgraded version is USD $109.

▲Avoid congestion

In view of the need for multiple users to read and write information together, Access is not a competitor of MySQL at all. Access lost when about 15 users were connected. We also heard that when only five people are connected, there will be some problems (this does not mean that only five people can connect to websites supported by Access at the same time ). Simultaneous connection is actually a concurrent process ). Therefore, although the number of links that Access can process is not limited, it does not matter as long as those links are kept within the scope of concurrent processing. For read-only websites (these websites are not the minority in your imagination), they can support up to 255 users. For large websites, it is inevitable that they must be upgraded to SQL Server to improve stability and efficiency.

Relatively speaking, MySQL sets a maximum of 100 users. However, we absolutely cannot use the built-in settings of a program to judge its effectiveness. So far, we have never heard of any complaints from users who use MySQL for large and frequently accessed websites. In addition, even if there is a large amount of data exchange on the network, it does not seem to have much impact on the query optimization of MYSQL.

Using the same hardware and data sizes on Windows 98, MySQL performs faster than Access 2000-but not in all cases. The efficiency of the two methods varies greatly in terms of data update. For the same data update, Access takes two times. If you process a small amount of data on a high-speed system, you will not notice the difference between the two. However, this difference in efficiency is obvious only when you process hundreds of thousands of pieces of data. MySQL will lose Access only when it processes the object structure. When creating tables and indexes, MySqL locks the tables, which slows down the processing of a large amount of data. However, the last problem mentioned above usually does not cause any problems during website development. Because on the website, we pay attention to the speed of queries when users visit, rather than the data storage structure itself. Therefore, MySQL wins in this field.

▲Other advantages of MYSQL

· Optimization

For MySQL optimization, we can say that the main problem lies in your hardware conditions, rather than MySQL itself. However, this is not the case with Access (and other desktop software. Yes, Microsoft Jet Database is indeed efficient, but it is not the fastest. If your design is very poor, your website will still be affected and the speed will slow down. The structure design also affects MySQL. For example, MySQL does not support foreign keys ). This disadvantage affects your design and website efficiency. For websites using MySQL, note that, how to minimize access IO, how to keep one or more CPUs at any time in the high-speed job status, and the appropriate network bandwidth, rather than the actual design and data query statements. In fact, some website developers refer to MySQL as the fastest running on the market. However, when you have many tables that need to be updated in a transaction process at the same time, MySQL does not run well.

· Backup

If you have had a painful experience in rescuing a damaged MDB file, you will be very grateful to MySQL. This is another advantage of MySQL over Access. First, mysqldump will generate a much better and more reliable backup file than Access. In contrast, in Access, you just copy an MDB file for backup. Second, even if the MySQL backup is partially damaged, it is much easier to restore than a corrupted MDB file.

· Scalability and data processing capability

For example, comparing Access to MySQL is like comparing the hills of the mountain with the Pike's Peak of the Colorado rocky mountains. The fact is as simple as this: MySQL can process much larger files than Access can process. If you obtain an Access 100 mb mdb file, you need to prepare a dictionary-thick record to record complaints from customers about low website efficiency. Similar running on MySQL won't show signs of heavy load.

In addition, MySQL provides a high degree of diversity and provides many different user interfaces, including command line client operations, Web browsers, and a variety of programming language interfaces, such as C ++, Perl, java, PHP, and Python. You can use a pre-packaged client or simply write an appropriate application on your own. MySQL can be used on Unix, Windows, OS/2, and other platforms, so it can be used on a personal computer or on.

That's right, Microsoft ActiveX Data Objects Library (ADO) does make Access more flexible in the external data market. It allows you to retrieve data without worrying about the location of the data, and then display the data on a public interface (that is, a Web browser. However, the disadvantage is that ADO is cumbersome after all (it is a hodgedge of resources) and it takes a lot of money and time to learn it, even if you are a good developer or software engineer. No one can learn ADO in one day.

▲Learning Curve

If you are familiar with the technology, you are basically no problem. A proficient person can learn MySQL within one day and add this experience to his resume. In contrast, Access is a much more complex development tool. It may take some time for a development engineer to have sufficient professional knowledge to use the software effectively.

As you expected, MySQL supports Structured Query Language (SQL ). If you have learned a certain version of the SQL language, you can do a lot better. Development engineers with VB or VBA knowledge may find that their previous ASP background can help them shorten their learning time.

▲Customer support

Although it is easy to use and free customer support does not exist, MySQL provides some electronic Group lists for your reference. Some are technical and members often provide the best customer support to each other-they share experiences and expertise with each other. In addition, you can purchase a version with customer support, including email support or telephone support. In general, the customer support rate is not fixed, so we cannot provide your price information.

▲Disadvantages of MySQL

Access is a relational Management System (RDBMS), but not at every layer of MySQL. This indicates that MySQL is not the best although it is very useful. The following list records the current relevance level and management level, which are not supported by MySQL:

MySQL cannot handle complex association functions, such as subqueries, although most subqueries can be rewritten to join. We expect this feature to be added when the next version is released.

Another feature that MySQL does not support is transaction processing and transaction commit/rollback ). A transaction refers to a group or set of commands that are jointly executed as a unit. If a transaction cannot be completed, no instruction in the transaction is actually executed. For commercial websites that must process online orders, MySQL does not support this function, which is indeed disappointing. However, you can use MaxSQL, a separate one. It supports transaction functions through external tables.

The foreign key (foreign key) and referential integrity (referential integrity) allow you to set constraints between data in the table, and then add the constraints to your specified data. These functions that MYSQL does not have indicate that an application that relies on complex data relationships is not suitable for MySQL. When we say that MySQL does not support foreign keys, we mean the reference integrity limit-MySQL does not support foreign key rules, and of course does not support cascading delete). In short, if your work requires complex data association, you should use the original Access.

You won't find the stored process (stored procedure) and trigger in MySQL ). (For these functions, Access provides the relative event procedure ).)

The GetRows function of Access provides better data picking.

Summary

The following table shows how MySQL, Access, and SQL Server are compared:

□Frequently accessed websites

· MySQL √

· Access √ **

· SQL Server √

□Complex Data Association

· MySQL ×

· Access √

· SQL Server √

□Online order processing

· MySQL √ *

· Access √ ***

· SQL Server √

□Compatibility

· MySQL ×

· Access √ ****

· SQL Server √

□Ease of use and operation

· MySQL √

· Access ×

· SQL Server ×

Note:

* MaxSQL

** If the data is read-only

* ** Additional functions obtained through Jet SQL

* *** Only ADO

If you need to use complicated resources and money, use SQL Server. If you still need complex but have no strong support, use Access. For other people, at least give MySQL a chance to use it!