[MySQL] MySQL v. S. SQL Server 2000 in Performance 5.0.24a

Take MySQL and SQL Server 2000 for a simple performance comparison test. MySQL 5.0 is used to test the program. It uses the Provider of ByteFX for MySQL.
1. Insert a record (No transaction) each time using parameterization ).

	Number of Records	Total Time	Average Number per Second
SQL Server 2000	81534	39''	2090
MySQL	81534	33 '2''	41.14

2. Join 100 Insert statements each time and submit them for execution (No parameterization is used, and each Batch insert, No transaction ).

	Number of Records	Total Time	Average Number per Second
SQL Server 2000	81534	33''	2470
MySQL	81456	32 '14''	42.12

3. The data in the same table structure is basically the same. The number of records in SQL Server 2000 is 81534, and that in MySQL is 81456.
In the following operation, MySQL uses the MySQL Query Browser, and the time is the total time (not that ??? Rows fetched in ??? S, but the one in the brackets ). SQL Server 2000 uses the query analyzer to perform operations, and uses Profiler to monitor the execution time.
For the Select * operation, the average time of SQL Server 2000 is 0.606 '', and that of MySQL is 0.0218 ''. Of course, the SQL Server 0.606 ''should include the total time for the storage engine to retrieve and transmit the returned data. The MySQL 0.0218'' is estimated to be only the time for the storage engine to retrieve data. In MySQL Query Browser, the Select * operation shows 81456 rows feched in 3.4213 s. This time should include the storage engine transmitting 81456 pieces of data to MySQL Query Browser, and the time when MySQL Query Browser is displayed. In this case, the storage engine is not very good between MySQL and SQL Server, but either it is MySQL's performance in the data transmission mechanism, or the cost of displaying MySQL Query Browser is too high.
In SQL Server 2000, use Primary Key (Clustered Index) to Select a record located in the middle of the table, and then Select the last record of the table using a unique non-indexed field (full table scan is required) test shows that SQL Server caches indexes and actual data pages. The results monitored by Profiler are not descriptive. However, SQL Server 2000 performs a full table scan for about 80 thousand of the table data or uses indexes to access the data. In general, the efficiency is relatively high.
Use the Primary Key Select statement in MySQL to display a record in the middle of the table. The average time is 0.0004 ''. Select, a non-indexed unique field, is located in the middle of the table, with an average usage of 0.096 ''each time ''. Use a non-indexed unique field to Select the last record of the table, with an average usage of 0.099 ''each time ''.
In MySQL, Select * from TableName limit 0.0015, 20, with an average of 0.0585 ''each time; Use limit 0.0935, 20, with an average of'' each time; Use limit, 20, with an average of ''each time ''. It seems that MySQL uses the row-by-row scan method to retrieve the specified record for the limit keyword, so the time used increases with the increase of the starting base.
In MySQL, when Select * from TableName order by Primary Key asc is used, the time of limit, 20, limit, 20, and limit is basically the same as the preceding test results. When Select * from TableName order by Primary Key desc is used, the time of limit 0.013, 20, limit, 20, and limit is basically '', which does not change much.
In MySQL, when the Select * from TableName order by non-index field desc is used, the limit 0.7, 20 time changes around 0.3 ''and; the time of limit 1.5, 20 is changed around the number 0.6 ''and 0.7''; the time of limit 1.7, 20 is changed between ''and.
From the test results above, MySQL has a very high efficiency in the Select aspect; the efficiency in the Order by aspect is average; the efficiency in the Insert data is very low.
Of course, from the perspective of the mechanism of SQL Server, performance problems basically occur where large memory operations are required, for example, Order operations, Hash/Merge Join operations, and Group By operations in the case of large data volumes. In the case of a large amount of data, the Join operation is not approved by myself. at the business level, the architecture design level or the program implementation level should be avoided as much as possible. However, Order and aggregation operations are frequently used. If you are interested, test the aggregation operations again.
The above test uses 80 thousand of the data volume, which is not big data. It is unclear what will happen in around 20 or 0.3 million. In fact, from the perspective of architecture design, tens of thousands or tens of thousands of Join operations can be avoided at any time. Around 20 or 0.3 million, full table scans, Order by, and Group By should be highly valued; 1 million, tens of millions of data, basically all access to the Table, must be performed through the index (for SQL Server 2000, it is best to use the Clustered index. Based on this premise, the performance of a general system using a MySQL database should still be able to cope.
As an open-source database, MySQL is still outstanding in terms of performance. I hope that MySQL will be continuously improved in the future, and the performance of commercial databases will be closer. As for the maintenance of database monitoring, debugging, tuning, and other development issues, I will not say much about it. I can endure it, but I still can pass through it. After all, it is open-source, free of charge.