DB Strengths Comparison: PostgreSQL vs MS SQL Server vs Oracle MySQL

As compared to PostgreSQL, Microsoft SQL Server and Oracle MySQL have many strengths. This includes high performance, low cost, easy to configure and learn, portability, and the source code is available. MySQL is fast. From the developer’s benchmark, it is shown that MySQL to be orders of magnitude faster than competition. MySQL is available at no cost, under an open source license. If required under a commercial license, it at low cost for your application.

Most modern databases use SQL (Sequential Query Language) and if have used another RDBMS (Relational Database Management System), then you should have no problem adapting to MySQL.

MySQL can be used on many different UNIX systems as well as Microsoft Windows making it portable and platform independent.

Relational databases are, by far, the most commonly used type of database. They depend on sound theoretical basis in relational algebra. Relational databases are made up of relations, commonly called tables. A table is a table of data just like in an electronic spreadsheet.

The table has columns and rows. Each column has a unique name and contains different data. Each column has an associated data type (more like the data types used in programming; integers, characters etc.). These columns can sometimes be called fields or attributes. Each row represents a different entity. For example, let’s say that we have a table for books in a library, then each row will represent a different book. These rows have the same attribute because of the tabular format. The rows are also called records of tuples.

Get quality help now

Sweet V

Verified

Proficient in: Database Management System

4.9 (984)

“ Ok, let me say I’m extremely satisfy with the result while it was a last minute thing. I really enjoy the effort put in. ”

+84 relevant experts are online

Hire writer

Each row consists of a set of individual values that correspond to columns. Each value must have the data type specified by its column. We need to have a way of identifying each book.

Names aren’t usually a good way of doing this because two books may have the same name. Due to this, we use a key (or primary key) as the identifying column in a table. A database may have two or more table. To relate these tables to one another we use the primary keys to form a communication between the tables, a communication within the database. A similar primary key in different tables is known as foreign key. This communication forms the relationship hence relational database.

Three basic kinds of relationships exist in a relational database, according to the number of things on each side of the relationship: one-to-one, one-to-many and many-to- many. One-to-one relationship, there is one of each thing in the relationship. One-to-many, one row in one table is linked to many rows in another table. Many-to-many, many rows in one table are linked to many rows in another table.

The MySQL software consists of the MySQL server, several utility programs that assist in the administration of MySQL databases and some supporting software that the MySQL server needs. The heart of the system is the MySQL server. MySQL server is the manager of the database system. It handles all the database instructions e.g. creating a new database, adding, updating, or data to that database. This can be done by sending messages to the MySQL server via SQL. The MySQL server continuously listens for messages that are directed to it.

All the interaction with the database is accomplished by passing messages to the MySQL server. The MySQL server must be able to understand the instructions that are sent to it, communicate using SQL (the standard language understood by most database management systems). Build an SQL query and send it to the MySQL server.

The only difficulty is when one has no or little understanding of the SQL. But if one has web server applications like XAMMP or WAMP, this can ease things. These applications have inbuilt SQL functions already available in the user interface.

MySQL itself is a relational database management system. The MySQL server can manage different databases at the same time. Each database consists of a structure to hold data and the data itself.

A MySQL system can have many users. Each user who needs to use the system, you will need to set up an account and a password. It isn’t compulsory to set up passwords for users, but it is strongly recommended that one sets up passwords for all users that are created for security reasons. And still on security reasons the root user (the user that comes default with each MySQL installation) should generally be used for administration purposes only.

MySQL supports a sophisticated privilege system. A privilege is the right to perform a particular action on a particular object and is associated with a particular user (similar to file permissions). When you create a user within MySQL, you grant her a set of privileges to specify what she can and cannot do within the system. GRANT and REVOKE commands to give and take away rights to and from MySQL users.

MySQL uses SQL. Structured Query Language is the most standard language for accessing relational database management systems (RDBMS). SQL is used to store and retrieve data to and from a database. It is used in other database systems such as Oracle, PostgreSQL, Sybase, and Microsoft SQL Server. MySQL offers enterprise level SQL features such as subqueries, views, and storage procedures allowing the database to be adopted from enterprise level.

MySQL offers many types of mechanisms for managing data, known as storage engines. MySQL supports several engines; MyISAM (default on all operating systems except Windows), MEMORY (the system memory. Attains the fastest response time possible), InnoDB (default on Windows, a robust transactional storage engine), FEDERATED (creates a local pointer to remote tables in a database network), ARCHIVE (used to compression of enormous amounts of data and uncompressing it only when necessary), CSV (store data in comma-separated format ), EXAMPLE (a basic template for understanding how storage engines are created), BLACKHOLE (just like MyISAM except that it won’t store any data) and MERGE (an aggregation of identical MyISAM tables).

Each storage engine bears its own strengths and weaknesses and should be applied selectively to best fit the intended use of your data. Because a single could consist of several tables, MySQL affords one an opportunity to simultaneously use different storage engines in a single database. MySQL version 3.23.23 added full-text indexing and searching which enhances the performance of mining data from text-based columns (CHAR, VARCHAR, and TEXT). This feature also enables you to produce results in order of relevance in accordance with how closely the query matches the row’s indexed textual columns.

MySQL offers query caching. Query caching causes MySQL to store SELECT queries (SELECT ‘DATABASE-NAME’), along with their corresponding results, in memory. As subsequent queries are executed, MySQL compares them against the cached queries; if they match, MySQL forgoes the costly database retrieval and instead simply dumps the cached query result.

From version 3.23.15 to the current version MySQL the replication feature was added. Replication allows a database located within one MySQL server to be duplicated on another MySQL server. Using this feature, just having a single slave database in place can greatly increase availability, because it could be immediately brought online if the master database experiences a problem. Also, backup is efficient, one can the backup on a slave while the master is online, allowing your application to incur zero down time.