Relation databases are the standard of the day.
But forimplementing a relational database from one of the industry standard companieslike Oracle or Microsoft, it becomes a costly affair. Thus to eradicate thisproblem, there are many companies which offer free RDBMS packages. Some ofthese packages are MySQL and PostgreSQL
These packagesprovide a very solid relational database system with almost the same set offeatures as the big ones but the main difference comes in performance andscalability. Any free software is normally started by enthusiasts andhobbyists. After a considerable amount of development, when the product becomesrobust, community accepts it and makes it their choice. There are manysuccesses in free software world like Linux, Sendmail, Perl, Apache and so on.MySQL is a similar system which is gaining influence in the community day byday, actually for some applications; it is rated as the best performer in termsof speed and reliability. In this section I am going to discuss a few aspectsof performance related issues with free packages like MySQL and discuss Prosand Cons
As mentioned earlier, MySQL is gaining industry fame day by day.Many big organizations are using MySQL. This makes it a bigger task for thedevelopers and administrators of this software to make it more responsive,reliable and increase the performance. The developers have stood up to thistask by providing various facilities that will enhance the performance. Thefollowing are notable thing that are done by developers of MySQL that makes ita better tool to work with:
. MySQL provides various table types which mayenhance the performance. All the user has to do is select the exact table typethat fits his application.
. Recently MySQL provided a thread library changein OpenBSD 3.0 to boost the performance and with introduction of BSD 5.0, itbecomes even faster.
. They provide large number of tools and wizardsto measure and tune the performance.
. A better way of doing Index merge: To satisfy anOR clause, MySQL is now able to create query plans that use multiple indexes.
. The ability to save server side procedures makesthe clients faster as they remove a huge work load from clients.
. The views provided by MySQL give huge advantageto the administrators and makes their task easier by providing features likeability to define relationships among multiple tables, ability to specifyfilter criteria and many others.
. Better tuning by settings likeoptimizer_prune_level and optimizer_search_depth.
. Server side cursors lets you define structuresor objects to hold retrieved information, some think like cache. This added tothe stored procedure concept, makes the performance of the application way toobetter.
. There are many triggers in Version 5.0.2 ofMySQL which will start activities on the server and thus makes the job ofdevelopers easier.
. Many features to report the workload on theserver and the client.
. These are just new features added into the MySQLversion 5.0, thus showing the development being done on MySQL to make itbetter. Apart from these existing features and functions that will enhance theperformance, many free workshops are conducted for betterment of performance orperformance tuning.
MySQL started as a low end free RDBMS system and quickly scaled upto a lightening fast database system. Many large organizations prefer MySQLover other costly relational database systems like oracle or Microsoft SQLserver; the primary reason being cost cutting and as MySQL is as efficient asothers. But there are many problems that come up while moving from costlysoftware like Oracle to free software like MySQL. The following is a discussionof performance problems that may result from using free RDBMS:
. The very first problem that may be encounteredis with scalability. For organizations that have huge databases, it may becomevery slow when trying to access data especially with lot of joins andaggregates.
. Complex updates like updating records wherethere are later records in the same table with the same ID are very hard tomanage in free software and are very slow. MySQL doesn't even support thisoperation as it doesn't have the required functions. It has to be done inmultiple steps which adds to the performance problem.
. The operations involving Group counts createmore performance issues, especially when the data to be grouped is huge. Eventhe count operation, which gives the number of records retrieved for aparticular column tends to be very slow too. Some of the free software does notsupport the count of group by operation which makes it even tougher as the useris limited to Order by option.
. As the default configuration may be not the bestone, loading data may take a huge time as compared to industry leaders. Takefor instance MySQL with its default table model. If you have to load 100,000records into this database (for example from existing databases like DB2 ororacle), you need to specify the exact table model and then upload the dataotherwise it will create a huge hindrance not only while loading but alsoduring retrieval.
. Complex count operations or grouping operationswhere in the SQL has to literally create joins in the database tables are veryslow as some of the functions required for the purpose are not developed.
White papers and discussion forms on www.Mysql.com