While subqueries are the principal new attraction in MySQL 4.1, there are some additional new features that you should be aware of.

INSERT ON DUPLICATE KEY UPDATE : Using this new option for INSERT statements, it’s possible to cause an insert that would duplicate a primary key to update the row matching that key instead. This means it’s no longer necessary for you to check in your application code to see if a given key exists; MySQL will handle this for you.

New GROUP_CONCAT() function: This function returns all the concatenated values from a group. The syntax is

GROUP_CONCAT([DISTINCT] expr [order-by-clause] [SEPARATOR separator-string])

Here, expr  is a column name or expression. Multiple expressions or columns (separated by commas) may be used. The optional order-by-clause follows the same rules for ORDER BY as used in a SELECT query. Also, an optional separator-string to be used in concatenating the values may be specified using the SEPARATOR keyword. Note that all of these arguments must be placed inside the parentheses following GROUP_CONCAT .

For example, referring to the same products table that we’ve been using in the previous sections, we could generate a list of prices of products for each product category as a single string, in which the names are separated by a colon with a space on either side of it as shown here:

Another enhancement from the viewpoint of efficiency is the addition of a new key cache system for MyISAM tables in MySQL 4.1.1 and a new command, CACHE INDEX , whose syntax is shown here:

CACHE INDEX table_name IN key_cache_name;

Prior to assigning indexes to a key cache, you must first create the key cache, as shown here:

SET GLOBAL key_cache_name.key_buffer_size = size;

This can also be done in the my.ini or my.cnf configuration file. The size parameter is an integer that is usually specified as a multiple of 1024 and some power of 8. Currently, all indexes from a table are assigned to a given key cache; eventually, it will be possible to assign only specified indexes to a cache.

By assigning table indexes to separate key caches, it’s possible to fine-tune MySQL’s performance by providing extra cache space for table indexes requiring it. The rationale behind this is more or less as follows: Tables normally “compete” for key cache space, and normally, this is a good thing. Tables that experience heavy usage will normally have their indexes kept in memory, and so MySQL will not have to retrieve their indexes from disk. However, you may have a table that is not used very often, but when it is queried, it’s very important that the query executes as quickly as possible. Using a key cache, you can guarantee that this table’s indexes will always be in memory and that MySQL won’t be slowed down by being required to read them in again from disk.

Finally, we should mention that the behavior for TIMESTAMP columns changes in MySQL 4.1.2 and above. From this release, it’s possible to create TIMESTAMP columns that default to the current date/time value and update this value whenever a record is updated. To illustrate this, let’s create a table named ts_test, as shown here:

In order to take advantage of the last_modified column’s properties in this regard, we must insert a null value into the column—using any other value, including 0 (zero) or the empty string, won’t work. Let’s insert a few rows into ts_test and see what happens:

When we select all the values in the table, this is what we see: Only the rows into which we inserted NULL actually stored the current date/time. Furthermore, we received no warning about the row into which we inserted a zero. Now let’s try updating three of the rows in ts_test:

Because of the ON UPDATE clause in the column definition, the last_modified column for any record in ts_test will be updated to the current date and time whenever that record is updated. However, if you set a timestamp column to an explicit value as part of an INSERT or UPDATE query, that value will be used instead:

 

---

 

NOTE  This auto-updating behavior for TIMESTAMP columns is effective only with tables created in MySQL 4.1.2 and above. If you’ve upgraded MySQL from a previous version and wish to take advantage
of this feature for an existing table (created using the previous version of MySQL), you’ll need to drop the table, re-create it, and reinsert any records that were present in the original.

---

Please be sure to come back next week for the next part of this article.

TIMESTAMP