What’s New in MySQL 5.1


What’s New in MySQL 5.1
Partitioning, Event scheduler are two big features that are introduced in this version.
and a major of sql optimization has been done.
MySQL 5.1 performs much better in all tests: it can handle more transactions per second and it does not deadlock with 256 threads, unlike 5.0.

The following features have been added to MySQL 5.1.


Partitioning. This capability enables distributing portions of individual tables across a file system, according to rules which can be set when the table is created. In effect, different portions of a table are stored as separate tables in different locations, but from the user point of view, the partitioned table is still a single table. Syntactically, this implements a number of new extensions to the CREATE TABLE, ALTER TABLE, and EXPLAIN … SELECT statements. As of MySQL 5.1.6, queries against partitioned tables can take advantage of partition pruning. In some cases, this can result in query execution that is an order of magnitude faster than the same query against a non-partitioned version of the same table. See Chapter 18, Partitioning, for further information on this functionality. (Author: Mikael Ronström)

Row-based replication. Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication. As of MySQL 5.1.5, another basis for replication is available. This is called row-based replication. Instead of sending SQL statements to the slave, the master writes events to its binary log that indicate how individual table rows are effected. As of MySQL 5.1.8, a third option is available: mixed. This will use statement-based replication by default, and only switch to row-based replication in particular cases. See Section 16.1.2, “Replication Formats”. (Authors: Lars Thalmann, Guilhem Bichot, Mats Kindahl)

Plugin API. MySQL 5.1 adds support for a very flexible plugin API that enables loading and unloading of various components at runtime, without restarting the server. Although the work on this is not finished yet, plugin full-text parsers are a first step in this direction. This allows users to implement their own input filter on the indexed text, enabling full-text search capability on arbitrary data such as PDF files or other document formats. A pre-parser full-text plugin performs the actual parsing and extraction of the text and hands it over to the built-in MySQL full-text search. See Section 22.2, “The MySQL Plugin Interface”. (Author: Sergey Vojtovich)

Event scheduler. MySQL Events are tasks that run according to a schedule. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler. See Section 19.4, “Using the Event Scheduler”. (Author: Andrey Hristov)

Server log tables. Before MySQL 5.1, the server writes general query log and slow query log entries to log files. As of MySQL 5.1, the server’s logging capabilities for these logs are more flexible. Log entries can be written to log files (as before) or to the general_log and slow_log tables in the mysql database. If logging is enabled, either or both destinations can be selected. The –log-output option controls the destination or destinations of log output. See Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”. (Author: Petr Chardin)

Upgrade program. The mysql_upgrade program (available as of MySQL 5.1.7) checks all existing tables for incompatibilities with the current version of MySQL Server and repairs them if necessary. This program should be run for each MySQL upgrade. See Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”. (Authors: Alexey Botchkov, Mikael Widenius)

MySQL Cluster. MySQL Cluster is now released as a separate product, based on MySQL 5.1 but with the addition of the NDBCLUSTER storage engine. Clustering support is no longer available in mainline MySQL 5.1 releases. MySQL Cluster releases are identified by a 3-part NDB version number; currently, the MySQL Cluster NDB 6.2 and MySQL Cluster NDB 6.3 release series are available for production use.

Some of the changes in MySQL Cluster since MySQL 5.0 are listed here:

MySQL Cluster replication. Replication between MySQL Clusters is now supported. It is now also possible to replicate between a MySQL Cluster and a non-cluster database. See Section 17.10, “MySQL Cluster Replication”.

MySQL Cluster disk data storage. Formerly, the NDBCLUSTER storage engine was strictly in-memory; now, it is possible to store Cluster data (but not indexes) on disk. This allows MySQL Cluster to scale upward with fewer hardware (RAM) requirements than previously. In addition, the Disk Data implementation includes a new “no-steal” restoration algorithm for fast node restarts when storing very large amounts of data (terabyte range). See Section 17.11, “MySQL Cluster Disk Data Tables”, for more information.

Improved backups for MySQL Cluster. A fault arising in a single data node during a Cluster backup no longer causes the entire backup to be aborted, as occurred in previous versions of MySQL Cluster.

Many other new features and improvements have been made to the NDBCLUSTER storage engine in MySQL Cluster NDB 6.2 and MySQL Cluster NDB 6.3; for more information about these, see Section 17.14, “MySQL Cluster Development Roadmap”.

Backup of tablespaces. The mysqldump utility now supports an option for dumping tablespaces. Use -Y or –all-tablespaces to enable this functionality.

Improvements to INFORMATION_SCHEMA. MySQL 5.1 provides much more information in its metadata database than was available in MySQL 5.0. New tables in the INFORMATION_SCHEMA database include FILES, EVENTS, PARTITIONS, PROCESSLIST, ENGINES, and PLUGINS.

XML functions with XPath support. ExtractValue() returns the content of a fragment of XML matching a given XPath expression. UpdateXML() replaces the element selected from a fragment of XML by an XPath expression supplied by the user with a second XML fragment (also user-supplied), and returns the modified XML. See Section 11.10, “XML Functions”. (Author: Alexander Barkov)

Load emulator. The mysqlslap program is designed to emulate client load for a MySQL server and report the timing of each stage. It works as if multiple clients were accessing the server. See Section 4.5.7, “mysqlslap — Load Emulation Client”. (Authors: Patrick Galbraith, Brian Aker)


Temporary Tables With MySQL

If you have had problems with MySQL not supporting functionality like sub selects this short article might be of interest for you. This article will explain how to use temporary tables with MySQL.

The juice

The SQL code below shows you how you can create a temporary table.

CREATE TEMPORARY TABLE TempTable ( ID int, Name char(100) ) TYPE=HEAP;
INSERT INTO TempTable VALUES( 1, “Foo bar” );
SELECT * FROM TempTable;

If you’re using an MySQL version older than 3.23, you should use the code below.

CREATE TABLE TempTable ( ID int, Name char(100) ) TYPE=HEAP;
INSERT INTO TempTable VALUES( 1, “Foo bar” );
SELECT * FROM TempTable;

Temporary tables

Temporary tables are per connection so heavy loaded sites will not have a problem with using the same table name since it’s unique per connection. If there already exists a table, which is not temporary, with the same same this table is then hidden until the temporary table is deleted. Temporary tables where added in MySQL version 3.23.

If you use an older version of MySQL than 3.23 you can’t use temporary tables, but you can use heap tables.

If the connection to the database is lost the temporary table is automatically deleted.

Heap tables

The heap tables are shared between all connections. Therefore you must use locking of the temporary table to ensure that no other connection is corrupting the data in your temporary table.

Since we need speed and the tables are temporary we create them as heap tables. This means that the tables are stored in memory and not on disk. Of course the data will be lost if the power is lost, but the kind of information stored in a heap table should be temporary data used for further queries.

To free memory always remember to drop the heap tables when you don’t need them anymore. Or at least delete the rows which is no longer of interest. Heap tables can take up a lot of memory if you don’t clean up after you’re done.

Heap tables are shared between all clients (just like any other table), so use them with care.

Using the LOCK TABLE mechanism

Since lock table works across connections this can be used to mimic the behaviour of the temporary tables. Although this is not that effective on servers with many connections, it works.

The lock table mechanism ensures that no other connections get access to write to the table so you don’t get corrupted data in the table while you are using it.

If the connection to the database is lost the locks are automatically unlocked.


Get Age Using Mysql Functions

Get Age Using Mysql Functions:


·         select (YEAR(CURRENT_DATE()) – YEAR(‘2004-05-05’) –


‘-‘, MONTH(‘2004-01-02’), ‘-‘, DAYOFMONTH(‘2008-01-02’))))) AS `age`


·         SELECT YEAR(DATE_SUB(NOW(), INTERVAL TO_DAYS(‘2004-01-02’) DAY)) AS age

MySQL 5.x: passing limits as stored procedure / function parameters


MySQL 5.x (at least 5.0.15 and earlier versions) does not allow using variables or procedure formal parameters with LIMIT. Here is a workaround:

  IN LimitStart_ INT,
  IN LimitCnt_ INT
  SET @lim = CONCAT(‘ LIMIT ‘, LimitStart_, ‘,’, LimitCnt_);
  SET @q = "SELECT mycol FROM mytable";

  SET @q = CONCAT(@q, @lim);


Three easy ways to optimize your MySQL queries

Any database programmer will tell you that in high-traffic database-driven applications, a single badly-designed SQL query can significantly impact the overall performance of your application. Not only does such a query consume more database time than it ideally should, but it can have an exponential effect on the performance of other application components.

Optimizing query performance is as much a black art as a science, as heavily dependent on the developer’s intuition as on hard statistical performance data. Fortunately, databases likes MySQL come with some tools to aid the process, and this article discusses three of them briefly: using indexes, analyzing queries with EXPLAIN, and adjusting MySQL’s internal configuration.

#1: Using indexes

MySQL allows you to index database tables, making it possible to quickly seek to records without performing a full table scan first and thus significantly speeding up query execution. You can have up to 16 indexes per table, and MySQL also supports multi-column indexes and full-text search indexes.

Adding an index to a table is as simple as calling the CREATE INDEX command and specifying the field(s) to index. Listing A shows you an example:

Listing A

mysql> CREATE INDEX idx_username ON users(username);
Query OK, 1 row affected (0.15 sec)
Records: 1 Duplicates: 0 Warnings: 0

Here, indexing the username field of the users table ensures that SELECT queries which reference this field in their WHERE or HAVING clause will run a little faster than in the pre-indexed state. You can check that the index was created (Listing B) with the SHOW INDEX command:

Listing B

mysql> SHOW INDEX FROM users;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| users | 1 | idx_username | 1 | username | A | NULL | NULL | NULL | YES | BTREE | |
1 row in set (0.00 sec)

It’s important to note that indexes are a double-edged sword. Indexing every field of a table is usually unnecessary, and is quite likely to slow things down significantly when inserting or updating data because of the additional work MySQL has to do to rebuild the index each time. On the other hand, avoiding indexes altogether isn’t such a great idea either, because while this will speed up INSERTs, it will cause SELECT operations to slow down. There is thus always a trade-off to be made, and it’s wise to consider what the primary function of the table will be (data retrieval or data edit) when designing the indexing system.

#2: Optimizing query performance

When analyzing query performance, it’s also useful to consider the EXPLAIN keyword. This keyword, when placed in front of a SELECT query, describes how MySQL intends to execute the query and the number of rows it will need to process to successfully deliver a result set. To illustrate, consider the following simple example (Listing C):

Listing C

mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = ‘IND’;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | country | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index |
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
2 rows in set (0.00 sec)

Here, the query is structured as a join between two tables and the EXPLAIN keyword describes how MySQL will process the join. It should be clear the current design will require MySQL to process only one record in the country table (which is indexed) but all 4079 records in the city table (which isn’t). This then suggests scope for improvement using other optimization tricks – for example, adding an index to the city table as follows (Listing D):

Listing D

mysql> CREATE INDEX idx_ccode ON city(countrycode);
Query OK, 4079 rows affected (0.15 sec)
Records: 4079 Duplicates: 0 Warnings: 0

And now, when you re-run the query with EXPLAIN, you’ll see a noticeable improvement (Listing E):

Listing E

mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = ‘IND’;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | country | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index |
| 1 | SIMPLE | city | ref | idx_ccode | idx_ccode | 3 | const | 333 | Using where |
2 rows in set (0.01 sec)

As this illustrates, MySQL now only needs to scan 333 records in the city table to produce a result set — a reduction of almost 90 percent! Naturally, this translates into faster query execution time and more efficient usage of database resources.

#3: Adjusting internal variables

MySQL is so open that it’s fairly easy to further fine-tune its default settings to obtain greater performance and stability. Some of the key variables that should be optimized are listed below.

  • Altering Index Buffer Size (key_buffer)
    This variable controls the size of the buffer used when handling table indices (both read and write operations). The MySQL manual recommends that this variable be increased “to as much as you can afford” to ensure you get the best performance on indexed tables, and recommends a value equivalent to about 25 percent of the total system memory. This is one of the more important MySQL configuration variables and if you’re interested in optimizing and improving performance, trying different values for the key_buffer_size variable is a good place to start.
  • Altering Table Buffer Size (read_buffer_size)
    When a query requires a table to be scanned sequentially, MySQL allocates a memory buffer to this query. The read_buffer_size variable controls the size of this buffer. If you find that sequential scans are proceeding slowly, you can improve performance by increasing this value, and hence the size of the memory buffer.
  • Setting The Number Of Maximum Open Tables (table_cache)
    This variable controls the maximum number of tables MySQL can have open at any one time, and thus controls the server’s ability to respond to incoming requests. This variable is closely related to the max_connections variables — increasing this value allows MySQL to keep a larger number of tables open, just as increasing max_connections increases the number of allowed connections. Consider altering this value if you have a high-volume server which receives queries on multiple different databases and tables.
  • Deciding A Time Limit For Long Queries (long_query_time)
    MySQL comes with a so-called “slow query log”, which automatically logs all queries that do not end within a particular time limit. This log is useful to track inefficient or misbehaving queries, and to find targets for optimization algorithms. The long_query_time variable controls this maximum time limit, in seconds.

The previous discussion should give you some insight into three tools you can use to analyze and optimize your SQL queries, and help you squeeze better performance out of your application. Go on and try them out — and happy optimizing!

(mysql.info)Command-line options

mysqld Command-line Options
`mysqld' accepts the following command-line options:
     Use ANSI SQL syntax instead of MySQL syntax.  Note: ANSI mode.
`-b, --basedir=path'
     Path to installation directory. All paths are usually resolved
     relative to this.
     Allow big result sets by saving all temporary sets on file.  It
     solves most 'table full' errors, but also slows down the queries
     where in-memory tables would suffice.  Since Version 3.23.2, MySQL
     is able to solve it automatically by using memory for small
     temporary tables and switching to disk tables where necessary.
     IP address to bind to.
     Directory where character sets are.  Note: Character sets.
     Chroot `mysqld' daemon during startup.  Recommended security
     measure. It will somewhat limit `LOAD DATA INFILE' and `SELECT ...
     INTO OUTFILE' though.
     Write a core file if `mysqld' dies.  For some systems you must also
     specify `--core-file-size' to `safe_mysqld'. *Note `safe_mysqld':
`-h, --datadir=path'
     Path to the database root.
     Set the default character set.  Note: Character sets.
     Set the default table type for tables.  Note: Table types.
     If MySQL is configured with `--with-debug', you can use this
     option to get a trace file of what `mysqld' is doing.  Note:
     Making trace files.
     Don't flush key buffers

 between writes for any `MyISAM' table.
     Note: Server parameters.
     Enable system locking.  Note that if you use this option on a
     system which a not fully working lockd() (as on Linux) you will
     easily get mysqld to deadlock.
     Enable support for named pipes (only on NT/Win2000/XP).
`-T, --exit-info'
     This is a bit mask of different flags one can use for debugging the
     mysqld server;  One should not use this option if one doesn't know
     exactly what it does!
     Flush all changes to disk after each SQL command.  Normally MySQL
     only does a write of all changes to disk after each SQL command
     and lets the operating system handle the syncing to disk.  Note:
`-?, --help'
     Display short help and exit.
     Read SQL commands from this file at startup.
`-L, --language=...'
     Client error messages in given language.  May be given as a full
     path.  Note: Languages.
`-l, --log[=file]'
     Log connections and queries to file. Note: Query log.
     Log all ISAM/MyISAM changes to file (only used when debugging
     Log all queries that have taken more than `long_query_time'
     seconds to execute to file.  Note: Slow query log.
     Log updates to `file.#' where `#' is a unique number if not given.
     Note: Update log.
     Log some extra information to update log.  If you are using
     `--log-slow-queries' then queries that are not using indexes are
     logged to the slow query log.
     Table-modifying operations (`INSERT'/`DELETE'/`UPDATE') will have
     lower priority than selects.  It can also be done via `{INSERT |
     REPLACE | UPDATE | DELETE} LOW_PRIORITY ...' to lower the priority
     of only one query, or by `SET OPTION SQL_LOW_PRIORITY_UPDATES=1'
     to change the priority in one thread.  Note: Table locking.
     Lock the `mysqld' process in memory.  This works only if your
     system supports the `mlockall()' system call (like Solaris).  This
     may help if you have a problem where the operating system is
     causing `mysqld' to swap on disk.
`--myisam-recover [=option[,option...]]] where option is any combination'
     of `DEFAULT', `BACKUP', `FORCE' or `QUICK'.  You can also set this
     explicitely to `""' if you want to disable this option. If this
     option is used, `mysqld' will on open check if the table is marked
     as crashed or if if the table wasn't closed properly.  (The last
     option only works if you are running with `--skip-locking').  If
     this is the case `mysqld' will run check on the table. If the
     table was corrupted, `mysqld' will attempt to repair it.
     The following options affects how the repair works.
     DEFAULT              The same as not giving any option to
     BACKUP               If the data table was changed during recover,
                          save a                     backup of the
                          `table_name.MYD' data file as
     FORCE                Run recover even if we will loose more than
                          one row                     from the .MYD file.
     QUICK                Don't check the rows in the table if there
                          isn't any                     delete blocks.
     Before a table is automatically repaired, MySQL will add a note
     about this in the error log.  If you want to be able to recover
     from most things without user intervention, you should use the
     options `BACKUP,FORCE'.  This will force a repair of a table even
     if some rows would be deleted, but it will keep the old data file
     as a backup so that you can later examine what happened.
     Path to pid file used by `safe_mysqld'.
`-P, --port=...'
     Port number to listen for TCP/IP connections.
`-o, --old-protocol'
     Use the 3.20 protocol for compatibility with some very old clients.
     *Note Upgrading-from-3.20::.
     Only use one thread (for debugging under Linux).  Note: Debugging
`-O, --set-variable var=option'
     Give a variable a value. `--help' lists variables.  You can find a
     full description for all variables in the `SHOW VARIABLES' section
     in this manual.  Note: SHOW VARIABLES.  The tuning server
     parameters section includes information of how to optimize these.
     Note: Server parameters.
     Skip some optimize stages.  Implies `--skip-delay-key-write'.
     Don't show databases for which the user doesn't have any
     If this is enabled, a user can't create new users with the GRANT
     command, if the user doesn't have `INSERT' privilege to the
     `mysql.user' table or any column in this table.
     Turn off the ability to select and insert at the same time on
     `MyISAM' tables. (This is only to be used if you think you have
     found a bug in this feature).
     Ignore the `delay_key_write' option for all tables.  Note: Server
     This option causes the server not to use the privilege system at
     all.  This gives everyone _full access_ to all databases!  (You
     can tell a running server to start using the grant tables again by
     executing `mysqladmin flush-privileges' or `mysqladmin reload'.)
     Never use host name cache for faster name-ip resolution, but query
     DNS server on every connect instead.  Note: DNS.
     Don't use system locking.  To use `isamchk' or `myisamchk' you must
     shut down the server.  Note: Stability.  Note that in MySQL
     Version 3.23 you can use `REPAIR' and `CHECK' to repair/check
     `MyISAM' tables.
     Hostnames are not resolved.  All `Host' column values in the grant
     tables must be IP numbers or `localhost'.  Note: DNS.
     Don't listen for TCP/IP connections at all.  All interaction with
     `mysqld' must be made via Unix sockets.  This option is highly
     recommended for systems where only local requests are allowed.
     Note: DNS.
     Don't use new, possible wrong routines.  Implies
     `--skip-delay-key-write'.  This will also set default table type
     to `ISAM'.  Note: ISAM.
     Don't delete or rename files that a symlinked file in the data
     directory points to.
     If MySQL is configured with `--with-debug=full', all programs will
     check the memory for overruns for every memory allocation and
     memory freeing.  As this checking is very slow, you can avoid
     this, when you don't need memory checking, by using this option.
     Don't allow 'SHOW DATABASE' commands, unless the user has *process*
     Don't write stack traces.  This option is useful when you are
     running `mysqld' under a debugger. Note: Debugging server.
     Disable using thread priorities for faster response time.
     Socket file to use for local connections instead of default
     Option can be any combination of: `REAL_AS_FLOAT',
     `ONLY_FULL_GROUP_BY'.  It can also be empty (`""') if you want to
     reset this

     By specifying all of the above options is same as using -ansi.
     With this option one can turn on only needed SQL modes. Note: ANSI
     Sets the default transaction isolation level.  Note: SET
`-t, --tmpdir=path'
     Path for temporary files. It may be useful if your default `/tmp'
     directory resides on a partition too small to hold temporary
`-u, --user=user_name'
     Run `mysqld' daemon as user `user_name'.  This option is
     _mandatory_ when starting `mysqld' as root.
`-V, --version'
     Output version information and exit.
`-W, --warnings'
     Print out warnings like `Aborted connection...' to the `.err' file.
     Note: Communication errors.