1.4 The main features of MySQL

The following list describes some of the important characteristics of MySQL:

  • Fully multi-threaded using kernel threads. That means it easily can use multiple CPUs if available.
  • C, C++, Eiffel, Java, Perl, PHP, Python and TCL APIs. 20 MySQL client tools and APIs.
  • Works on many different platforms. 4.2 Operating systems supported by MySQL.
  • 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. 7.2 Column types.
  • Very fast joins using an optimized 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-optimized class library and should be as fast as they can get! Usually there shouldn't be any memory allocation at all after query initialization.
  • Full support for 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 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 which is very flexible and secure, and which allows host-based verification. Passwords are secure since all password traffic when connecting to a server is encrypted.
  • ODBC (Open-DataBase-Connectivity) for Windows95 (with source). All ODBC 2.5 functions and many others. You can, for example, use Access to connect to your MySQL server. 16 MySQL ODBC Support.
  • Very fast B-tree disk tables with index compression.
  • 16 indexes per table are allowed. Each index may consist of 1 to 16 columns or parts of columns. The maximum index length is 256 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.
  • 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 isamchk, a very fast utility for table checking, optimization and repair. 13 Maintaining a MySQL installation.
  • Full support for the ISO-8859-1 Latin1 character set. For example, the Scandinavian characters @ringaccent{a}, @"a and @"o are allowed in table and column names.
  • All data are saved in ISO-8859-1 Latin1 format. All comparisons for normal string columns are case insensitive.
  • Sorting is done according to the ISO-8859-1 Latin1 character set (the Swedish way at the moment). It is possible to change this in the source by adding new sort order arrays. To see an example of very advanced sorting, look at the Czech sorting code. MySQL supports many different character sets that can be specified at compile time.
  • Aliases on tables and columns as in the SQL92 standard.
  • DELETE, INSERT, REPLACE, and UPDATE return how many rows were changed (affected).
  • 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. 7.30 Is MySQL picky about reserved words?.
  • 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. 9.1 What languages are supported by MySQL?.
  • Clients connect to the MySQL server using TCP/IP connections or Unix sockets, or named pipes under 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 optimizer resolves a query.