1.9.4 New Features Planned For The Near Future

New functionality

• Oracle-like CONNECT BY PRIOR ... to search tree-like (hierarchical) structures.
• Add all missing SQL-92 and ODBC 3.0 types.
• Add SUM(DISTINCT).
• INSERT SQL_CONCURRENT and mysqld --concurrent-insert to do a concurrent insert at the end of the file if the file is read-locked.
• Allow update of variables in UPDATE statements. For example: UPDATE TABLE foo SET @a=a+b,a=@a, b=@a+c.
• Change when user variables are updated so that one can use them with GROUP BY, as in the following example: SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.
• Add an IMAGE option to LOAD DATA INFILE to not update TIMESTAMP and AUTO_INCREMENT fields.
• Added LOAD DATA INFILE ... UPDATE syntax.
  • For tables with primary keys, if the data contains the primary key, entries matching that primary key are updated from the remainder of the columns. However, columns missing from the incoming data feed are not touched.
  • For tables with primary keys that are missing some part of the key in the incoming data stream, or that have no primary key, the feed is treated as a LOAD DATA INFILE ... REPLACE INTO now.
• Make LOAD DATA INFILE understand syntax like:

  LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name
       TEXT_FIELDS (text_field1, text_field2, text_field3)
       SET table_field1=CONCAT(text_field1, text_field2),
           table_field3=23
       IGNORE text_field3
  

  This can be used to skip over extra columns in the text file, or update columns based on expressions of the read data.
• New functions for working with SET type columns:
  • ADD_TO_SET(value,set)
  • REMOVE_FROM_SET(value,set)
• If you abort mysql in the middle of a query, you should open another connection and kill the old running query. Alternatively, an attempt should be made to detect this in the server.
• Add a storage engine interface for table information so that you can use it as a system table. This would be a bit slow if you requested information about all tables, but very flexible. SHOW INFO FROM tbl_name for basic table information should be implemented.
• Allow SELECT a FROM crash_me LEFT JOIN crash_me2 USING (a); in this case a is assumed to come from the crash_me table.
• DELETE and REPLACE options to the UPDATE statement (this will delete rows when one gets a duplicate key error while updating).
• Change the format of DATETIME to store fractions of seconds.
• Make it possible to use the new GNU regexp library instead of the current one (the GNU library should be much faster than the old one).

Standards compliance, portability and migration

• Don't add automatic DEFAULT values to columns. Give an error when using an INSERT that doesn't contain a column that doesn't have a DEFAULT.
• Add ANY(), EVERY(), and SOME() group functions. In standard SQL these work only on boolean columns, but we can extend these to work on any columns/expressions by applying: value == 0 -> FALSE and value <> 0 -> TRUE.
• Fix that the type for MAX(column) is the same as the column type:

  mysql> CREATE TABLE t1 (a DATE);
  mysql> INSERT INTO t1 VALUES (NOW());
  mysql> CREATE TABLE t2 SELECT MAX(a) FROM t1;
  mysql> SHOW COLUMNS FROM t2;
  

Speed enhancements

• Don't allow more than a defined number of threads to run MyISAM recover at the same time.
• Change INSERT ... SELECT to optionally use concurrent inserts.
• Add an option to periodically flush key pages for tables with delayed keys if they haven't been used in a while.
• Allow join on key parts (optimisation issue).
• Add simulation of pread()/pwrite() on Windows to enable concurrent inserts.
• A logfile analyser that could parse out information about which tables are hit most often, how often multi-table joins are executed, etc. It should help users identify areas or table design that could be optimised to execute much more efficient queries.

Internationalisation

Usability enhancements

• Return the original field types() when doing SELECT MIN(column) ... GROUP BY.
• Make it possible to specify long_query_time with a granularity in microseconds.
• Link the myisampack code into the server, enabling a PACK or COMPRESS command on the server.
• Add a temporary key buffer cache during INSERT/DELETE/UPDATE so that we can gracefully recover if the index file gets full.
• If you perform an ALTER TABLE on a table that is symlinked to another disk, create temporary tables on this disk.
• Implement a DATE/DATETIME type that handles time zone information properly so that dealing with dates in different time zones is easier.
• Fix configure so that one can compile all libraries (like MyISAM) without threads.
• Allow SQL variables in LIMIT, like in LIMIT @a,@b.
• Automatic output from mysql to a web browser.
• LOCK DATABASES (with various options).
• Many more variables for SHOW STATUS. Records reads and updates. Selects on 1 table and selects with joins. Mean number of tables in select. Number of ORDER BY and GROUP BY queries.
• mysqladmin copy database new-database; requires COPY command to be added to mysqld.
• Processlist should show number of queries/threads.
• SHOW HOSTS for printing information about the hostname cache.
• Change table names from empty strings to NULL for calculated columns.
• Don't use Item_copy_string on numerical values to avoid number->string->number conversion in case of: SELECT COUNT(*)*(id+0) FROM table_name GROUP BY id
• Change so that ALTER TABLE doesn't abort clients that execute INSERT DELAYED.
• Fix so that when columns are referenced in an UPDATE clause, they contain the old values from before the update started.

New operating systems

• Port of the MySQL code to QNX.
• Port of the MySQL clients to LynxOS.

User Comments

I think the Oracle-like CONNECT BY PRIOR function would be very helpful. Thank you.

I also think that CONNECT BY would solve many functional problems.

thx, tomaz

If CONNECT BY isn't going to be implemented SOON, can someone reference a work around? Unfortunately most of what's on the WWW also requires features not present in MySQL (procedures, subselects [for V3.23], etc.).

Why "connect by"? SQL99 allows recursive queries with "WITH clause" which is standard way of doing the same thing.

How about an easier way of modifying a set/enum definition? There are times when I want to add an additional option to one of these types and to do so I must type in the whole enum over again, plus the additional value.

Add your own comment.