You are here

Home › Technology › Web Sites › Databases ›

MySQL

Submitted by Peter on Thu, 2011-06-16 13:00

mysql logo showing their dolphin

MySQL is the first choice for most database uses. MySQL is free. The documentation for MySQL is free. You can buy training and support if you need it. MySQL gives you the choice of several storage engines for different types of applications.

Multiple storage engines

All uses of a database require a planning stage where you choose the optimum approach. MySQL offers you more choices which means more planning is useful. One of the choices is the right storage engine. A storage engine is the individual code managing a specific type of database. The old default MyISAM was the fastest database for reads and does not have transactions, a limitation that eliminates MyISAM for several types of applications. MySQL now includes InnoDB, InnoDB provides transactions, and InnoDB is the default storage engines in the latest versions of MySQL.

Contact me when you need help with planning planning a new Web site.

Zero cost for small to medium web sites

MySQL's zero purchase cost frees up your budget for planning, training and testing. MySQL has a lower resource usage than some other databases which means you can keep the same hardware for better performance or cut hardware costs. Your choice of storage engine may alter resource usage.

Documentation is free and online for reading any time. Oracle now owns MySQL and provides paid training courses plus paid consultations. There are other organisations providing training and consulting for MySQL. When you use a well designed application, you can use the application without knowing much about the database behind the application. Web sites are a common use for MySQL. The Web hosting company installs MySQL. You use a Web administration page to create a database for your application. The application creates the tables. The application backs up the data from the database. You do not need to think about the database until your Web site grows too large for one server.

MySQL on multiple servers is an area where you need training or consultants and you often need commercial extensions to MySQL to manage all that data. A small investment in a consultation on your single server web site may delay the expense of a multiple server configuration for a long time.

Installation

Ubuntu Linux installs MySQL through the Ubuntu Software Centre. For other operating systems, download MySQL at dev.mysql.com/downloads.

Releases

You will hear about MySQL 4.0, 4.1, 5.0, 5.1, 5.5, and 5.6. 4.0 was an old version you should not use. 4.1 was a major upgrade including a significant change to the native way of storing passwords, a change that caused a few problems. 5.0 brought a bigger better range of SQL then 5.1 arrived with more significant improvements. 5.1 should be the minimum version to use.

5.5 swaps the default storage engine from MyISAM to InnoDb and upgrades InnoDB to work better with multiple core processors. Your next upgrade should be to 5.5 using your current table types. After the conversion to 5.5, start converting from MyISAM to InnoDB. Some things will be faster in InnoDB and some things might be slower. Overall you should be better off with InnoDB.

5.6 is next and brings more improvements to InnoDB to speed up InnoDB when used with multiple core processors. There is little advantage for small web sites using a server with one processor and only four cores in the processor. The real advantage of 5.6 is for Web sites using a dedicated server with multiple processors in the server and many cores in each processor.

Do you develop applications? Write your SQL for 5.1 because many people are still on 5.1. The main differences in 5.5 and 5.6 are GIS functions. if you are writing an application that uses GIS data, you might want to push people toward 5.6.

Alternatives

PostgreSQL was, for a long time, the one real alternative. PostgreSQL had transactions and a lot of other features before MySQL. A lot of sites started with MySQL then migrated to PostgreSQL when the sites added shopping carts.

InnoDB was a separate choice and is now part of MySQL. InnoDB offers transactions and kills the need for PostgreSQL in most Web sites.

SQLite is a special small database used in applications to store settings. For example, Firefox uses SQLite to store bookmarks. You could also use SQLite to store temporary data. MySQL offers two choices for storing temporary data including a memory based database that would be faster than SQLite in some circumstances.

Interest in alternatives increased when Sun purchased MySQL and again when Oracle purchased Sun. Sun was only ever going to damage MySQL by using Java. Oracle might limit MySQL to stop MySQL competing against Oracle. Currently MySQL is proceeding through several new releases under Oracle and all the updates are beneficial. The Oracle ownership is producing better results than the Sun ownership.

MariaDB is the new alternative, is developed by some of the original MySQL developers who left MySQL when Sun purchased MySQL, is currently plug compatible, and is gaining the attention of MySQL users. If your Web hosting company supplies MariaDB instead of MySQL, you will currently end up with the same result. MariaDB might go in a different direction in the future.

SQL

If you are a software developer and want to talk to MySQL, or any other relational database, using a simple text based request, you learn SQL, the Structured Query Language. There is more information about SQL in the the page named SQL.

Further reading

User login

All the content of this site is under the copyright of PeterMoulding.com except where explicitly stated otherwise. Contact us to buy articles or images.