MySQL

What is MySQL

MySQL is a Relational Database Management System. A relational database adds speed and flexibility, by storing data in separate tables rather than putting all the data in one area. These tables are linked by defined relations making it possible to combine data from several tables upon request. Using a RDMS means it is possible to add, access, and process the data stored in your database. ‘SQL’ stands for “Structured Query Language” – the most common standardised language used to access databases. MySQL is Open Source software and is freely available at www.mysql.com. Open Source software means that the source code can easily be manipulated and modified by anyone. It is very simple to use.

Why use MySQL?

MySQL is very fast, reliable, and easy to use. MySQL also has a very practical set of features developed in close cooperation with its users. It is also Open Source and therefore freely accessible. MySQL is used to access databases on the internet due to its connectivity, speed and security. It was originally developed to manage large databases at a much faster speed than the solutions that previously existed. MySQL has for several years, been thriving in the challenging areas of production.

Features

Below are some of the important characteristics of MySQL and what it supports as listed at

http://www.mysql.com/doc/F/e/Features.html:

  • Fully multi-threaded using kernel threads. That means it can easily use multiple CPUs if available.
  • Works on many different platforms. C, C++, Eiffel, Java, Perl, PHP, Python and Tcl APIs.
  • Many column types: signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, and ENUM types.
  • Very fast joins using an optimised one-sweep multi-join.
  • Full operator and function support in the SELECT and WHERE parts of queries. Example: mysql> SELECT CONCAT(first_name, ” “, last_name) FROM tbl_name WHERE income/dependents > 10000 AND age > 30;
  • SQL functions are implemented through a highly optimised class library and are very fast. Usually there should not be any memory allocation at all after query initialisation.
  • Supports SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT(), COUNT(DISTINCT), AVG(), STD(), SUM(), MAX() and MIN()).
  • Support for LEFT OUTER JOIN and RIGHT OUTER JOIN with ANSI SQL and ODBC syntax.
  • You can mix tables from different databases in the same query (as of Version 3.22).
  • A privilege and password system that is very flexible and secure and allows host-based verification. Passwords are secure because all password traffic is encrypted when you connect to a server.
  • ODBC (Open-DataBase-Connectivity) support for Win32 (with source). All ODBC 2.5 functions and many others. For example, you can use MS Access to connect to your MySQL server.
  • Very fast B-tree disk tables with index compression.
  • Up to 32 indexes per table are allowed. Each index may consist of 1 to 16 columns or parts of columns. The maximum index length is 500 bytes (this may be changed when compiling MySQL). An index may use a prefix of a CHAR or VARCHAR field.
  • Fixed-length and variable-length records.
  • In-memory hash tables which are used as temporary tables.
  • Handles large databases. We are using MySQL with some databases that contain 50,000,000 records and we know of users that uses MySQL with 60,000 tables and about 5,000,000,000 rows
  • All columns have default values. You can use INSERT to insert a subset of a table’s columns; those columns that are not explicitly given values are set to their default values.
  • Uses GNU Automake, Autoconf, and libtool for portability.
  • Written in C and C++. Tested with a broad range of different compilers.
    A very fast thread-based memory allocation system.
  • No memory leaks. Tested with a commercial memory leakage detector (purify).
    Includes myisamchk, a very fast utility for table checking, optimisation, and repair.
  • Supports several different character sets, including ISO-8859-1 (Latin1) and more.
  • All data are saved in the chosen character set. All comparisons for normal string columns are case insensitive.
  • Sorting is done according to the chosen character set (the Swedish way by default). It is possible to change this when the MySQL server is started up. MySQL supports many different character sets that can be specified at compile and run time.
  • Aliases on tables and columns are allowed as in the SQL92 standard.
  • DELETE, INSERT, REPLACE, and UPDATE return how many rows were changed (affected). It is possible to return the number of rows matched instead by setting a flag when connecting to the server.
  • Function names do not clash with table or column names. For example, ABS is a valid column name. The only restriction is that for a function call, no spaces are allowed between the function name and the `(‘ that follows it.
  • All MySQL programs can be invoked with the –help or -? options to obtain online assistance.
  • The server can provide error messages to clients in many languages.
  • Clients may connect to the MySQL server using TCP/IP Sockets, Unix Sockets (Unixes), or Named Pipes (NT).
  • The MySQL-specific SHOW command can be used to retrieve information about databases, tables, and indexes. The EXPLAIN command can be used to determine how the optimiser resolves a query.

Leave a Reply