Special Interest Activity

ITK 478

BHALCHANDRA

SPECIAL INTEREST ACTIVITY

FALL 07

TOPIC: Comparison between MySQL and PostgreSQL for ease of installation and administration from a layman’s perspective.

Introduction and Background

Database management systems are important for any business and today we find every mom and pop business going online. In this hands on activity I am trying to evaluate open source DBMS options MySQL and PostgreSQL from the point of view of a small E-Commerce business (basically a business where there is minimal IT related knowledge). Both these database are industrial strength databases and enjoy wide following. Craigslist, Yahoo, Wikipedia, Flickr use MySQL and Genetech, BASF, Skype use PostgreSQL . Any E -commerce business irrespective of its size needs to store data about 1) Products offered 2) Customer related information 3) Order status 4) Visitor browser behavior etc. also sometimes there is a need to embed a database in the software solutions that the company is offering.

Activity Abstract:

In this both the databases were installed on machine/s with Windows OS

1) XP Home Edition and 2) Vista Professional.

Tables were created and the experience was compared from point of view of an application similar to tumble book.com

In my position paper “MySQL vs PostgreSQL for a Small Scale E-Commerce Business.” I came to a conclusion that there is not much to differentiate in these two options based on technical issues ( w.r.t. our application) however issues like ease of installation, ease of administration, availability of support in case we run into trouble are going to count a lot especially when you are not an IT person.

So to get first hand feel of what can happen as far as our application is concerned I installed both the databases on my laptop having Windows Vista Business and on my desktop having Windows XP Home Edition.

Tables were created and I tried to store the swf files as blob type in tables. Now there are arguments in favor of this and against this but instead of getting into those details I decided to save blobs in tables.

The max size of blob that is to be stored is 12MB.

HANDS ON COMPONENT

DATABASE INSTALLATION

Download MySQL and MySQL Administrator

Table creation and data insertion in MySQL

1) CLICK ON “Catalogs” IN MYSQL ADMINISTRATOR

2) RIGHT CLICK ON “test” SELECT “Create New Schema”

3) Enter table name say “myfirsttable” and with “columns and indices” tab selected add columns say “col1” select appropriate data type. Click on “Apply Changes”

6) Click on “Execute”

7) Click on “Tools” Select “MySQL Query Browser” Click it

8) Select the table from Myfirstschema and run select * query….by clicking on the “Execute”Button

9) To add data select field and click “Edit”

10) Click on “Apply Changes”

11) To add data in LONGBLOB field

http://dev.mysql.com/doc/query-browser/en/mysql-query-browser-using-blobs.html

IMPORTANT: In MySQL Administrator Select “Startup Variables”, Select “Advanced Networking” tabs and adjust “Max Packet Size” adjust according to need and Click “Apply Changes”

Select file and click “Open” and click on “Apply Changes”

Issues encountered in installation

1) For the records after installing both the databases on XP Machine I had to format the disc as CPU Usage was constantly 100%. Now this may or may not be related to installation.

2) Installation of MySQL on Windows Vista Business was possible only after turning User Account Control Off.

MySQL Administrator is an excellent tool that allows you easy creation of tables, data editing, User administration etc.

http://www.mysql.com/products/tools/administrator/

Download PostgreSQL and pgAdminIII

CREATION OF TABLES IN PostgreSQL

Open pgAdminIII and double click on selected database server, enter password on the popped up window and click OK

Now to insert data in our newly created table Click on “Tools” select “Scripts” and select “INSERT Script”

Or alternately

Issues involved in download and table creations

1) There are so many options are available for downloading that it is quite confusing to decide the right download.

2) In PostgreSQL for insertion of “blob” pgAdminIII does not provide any sort of drag and drop way I tried using Navicat also but that also offers no convenient way of doing blob insertion.

Installation of PostgreSQL along with pgAdminIII was an easy job. As far as table creation and User administration was concerned; for normal tables involving the usual data types like integer, varchar; table creation and data insertion was quite simple, however since we have to insert swf files in our table there is no convenient way to do it and when I say convenient I mean sort of drag and drop. The other tool Navicat for PostgreSQL also do not provide any convenient way to insert blobs in a table.

User community and available resources.

If we search for word PostgreSQL in Title of book at Milner we get 3 results

A similar search for MySQL returns 35 results

Now this cannot be called as true reflection of support available for each database but can be considered as a good indicator of the same.

Conclusion:

Both the databases are easy to install and administer but if we must compare, then MySQL is a bit more convenient.

For our application MySQL works out better, thanks to MySQL Administrator, we could actually create a table containing our swf files which on extraction from the table displayed the content as expected.

Despite trying for a long time I am not able to find a convenient way to insert blobs in postgreSQL and I will continue my search in this regard. As far as MySQL is concerned I encountered no such issue which made me search the web for a solution.

So if I have to pick a choice at this juncture MySQL is a clear winner.

REF: