1.6 MySQL and the Future (the TODO)

This section summarizes the features that we plan to implement in MySQL Server. The items are ordered by release series. Within a list, items are shown in approximately the order they will be done.

Note: If you are an enterprise-level user with an urgent need for a particular feature, please contact [e-mail] to discuss sponsoring options. Targeted financing by sponsor companies allows us to allocate additional resources for specific purposes. One example of a feature sponsored in the past is replication.

1.6.1 New Features Planned for 4.1

The following features are not yet implemented in MySQL 4.1, but are planned for implementation as MySQL 4.1 moves into its beta phase. For a list what is already done in MySQL 4.1, see Section 1.5.2.1, "Features Available in MySQL 4.1."

• Stable OpenSSL support (MySQL 4.0 supports rudimentary, not 100% tested , support for OpenSSL).

• More testing of prepared statements.

• More testing of multiple character sets for one table.

1.6.2 New Features Planned for 5.0

The following features are planned for inclusion into MySQL 5.0. Some of the features such as stored procedures are complete and are included in MySQL 5.0 alpha, which is available now. Others such as cursors are only partially available. Expect these and other features to mature and be fully supported in upcoming releases.

Note that because we have many developers that are working on different projects, there will also be many additional features. There is also a small chance that some of these features will be added to MySQL 4.1. For a list what is already done in MySQL 4.1, see Section 1.5.2.1, "Features Available in MySQL 4.1."

For those wishing to take a look at the bleeding edge of MySQL development, we make our BitKeeper repository for MySQL version 5.0 publicly available. As of December 2003, binary builds of version 5.0 are also available.

• Stored Procedures

  • Stored procedures currently are implemented, based on the SQL:2003 standard.

• New functionality

  • Elementary cursor support.

  • The ability to specify explicitly for MyISAM tables that an index should be created as an RTREE index. (In MySQL 4.1, RTREE indexes are used internally for geometrical data that use GIS data types, but cannot be created on request.)

  • Dynamic length rows for MEMORY tables.

• Standards compliance, portability and migration

  • Add true VARCHAR support (column lengths longer than 255, and no stripping of trailing whitespace). There is already support for this in the MyISAM storage engine, but it is not yet available at the user level.

• Speed enhancements

  • SHOW COLUMNS FROM tbl_name (used by the mysql client to allow expansions of column names ) should not open the table, only the definition file. This will require less memory and be much faster.

  • Allow DELETE on MyISAM tables to use the record cache. To do this, we need to update the threads record cache when we update the .MYD file.

  • Better support for MEMORY tables:

    • Dynamic length rows.

    • Faster row handling (less copying).

• Usability enhancements

  • Resolving the issue of RENAME TABLE on a table used in an active MERGE table possibly corrupting the table.

The news section of the online manual includes a more in-depth list of features. See http://dev.mysql.com/doc/mysql/en/News.html.

1.6.3 New Features Planned for 5.1

• New functionality

  • FOREIGN KEY support for all table types, not just InnoDB .

  • Column-level constraints.

  • Online backup with very low performance penalty. The online backup will make it easy to add a new replication slave without taking down the master.

• Speed enhancements

  • New text-based table definition file format ( .frm files) and a table cache for table definitions. This will enable us to do faster queries of table structures and do more efficient foreign key support.

  • Optimize the BIT type to take one bit. ( BIT now takes one byte; it is treated as a synonym for TINYINT .)

• Usability enhancements

  • Add options to the client/server protocol to get progress notes for long running commands.

  • Implement RENAME DATABASE . To make this safe for all storage engines, it should work as follows :

    1. Create the new database.

    2. For every table, do a rename of the table to another database, as we do with the RENAME command.

    3. Drop the old database.

  • New internal file interface change. This will make all file handling much more general and make it easier to add extensions like RAID.

1.6.4 New Features Planned for the Near Future

• New functionality

  • Views, implemented in stepwise fashion up to full functionality.

  • Oracle-like CONNECT BY PRIOR to search tree-like (hierarchical) structures.

  • Add all missing standard SQL and ODBC 3.0 types.

  • Add SUM(DISTINCT) .

  • INSERT SQL_CONCURRENT and mysqld --concurrent-insert to do a concurrent insert at the end of a table if the table is read-locked.

  • Allow variables to be updated in UPDATE statements. For example: UPDATE foo SET @a:=a+b,a=@a, b=@a+c .

  • Change when user variables are updated so that you can use them with GROUP BY , as in the following statement: SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM tbl_name GROUP BY id .

  • Add an IMAGE option to LOAD DATA INFILE to not update TIMESTAMP and AUTO_INCREMENT columns.

  • Add LOAD DATA INFILE ... UPDATE syntax that works like this:

    • For tables with primary keys, if an input record contains a primary key value, existing rows matching that primary key value are updated from the remainder of the input columns. However, columns corresponding to columns that are missing from the input record are not touched.

    • For tables with primary keys, if an input record does not contain the primary key value or is missing some part of the key, the record is treated as LOAD DATA INFILE ... REPLACE INTO .

  • Make LOAD DATA INFILE understand syntax like this:

    
    

     LOAD DATA INFILE '  file_name  .txt' INTO TABLE  tbl_name  TEXT_FIELDS (  text_col1, text_col2, text_col3  )      SET  table_col1  =CONCAT(  text_col1, text_col2  ),  table_col3  =23      IGNORE  text_col3  

    
    

    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 tbl_name1 LEFT JOIN tbl_name2 USING (a) ; in this case a is assumed to come from tbl_name1 .

  • DELETE and REPLACE options to the UPDATE statement (this will delete rows when a duplicate-key error occurs 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 new library should be much faster than the current one).

• Standards compliance, portability and migration

  • Don't add automatic DEFAULT values to columns. Produce an error for any INSERT statement that is missing a value for a column that has no 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 or expressions by treating a value of zero as FALSE and non-zero values as TRUE .

  • Fix the type of MAX( column ) to be 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 recovery at the same time.

  • Change INSERT INTO... 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 (optimization issue).

  • Add a log file analyzer that can extract information about which tables are hit most often, how often multiple-table joins are executed, and so on. This should help users identify areas of table design that could be optimized to execute much more efficient queries.

• Usability enhancements

  • Return the original column 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 so that it can perform PACK or COMPRESS operations.

  • 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 that disk.

  • Implement a DATE/DATETIME type that handles time zone information properly, to make dealing with dates in different time zones easier.

  • Fix configure so that all libraries (like MyISAM ) can be compiled without threads.

  • Allow user variables as LIMIT arguments; for example, LIMIT @a,@b .

  • Automatic output from mysql to a Web browser.

  • LOCK DATABASES (with various options).

  • Many more variables for SHOW STATUS . Record reads and updates. Selects on a single table and selects with joins. Mean number of tables in selects. Number of ORDER BY and GROUP BY queries.

  • mysqladmin copy database new-database ; this requires a COPY operation to be added to mysqld .

  • Processlist output should indicate the 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-to-string-to-number conversion in case of SELECT COUNT(*)*(id+0) FROM tbl_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 the MySQL clients to LynxOS.

1.6.5 New Features Planned for the Mid-Term Future

• Implement function: get_changed_tables( timeout,table1,table2 ,...) .

• Change reading through tables to use mmap() when possible. Now only compressed tables use mmap() .

• Make the automatic timestamp code nicer. Add timestamps to the update log with SET TIMESTAMP= val ; .

• Use read/write mutex in some places to get more speed.

• Automatically close some tables if a table, temporary table, or temporary file gets error 23 (too many open files).

• Better constant propagation. When an occurrence of col_name = n is found in an expression, for some constant n , replace other occurrences of col_name within the expression with n . Currently, this is done only for some simple cases.

• Change all const expressions with calculated expressions if possible.

• Optimize key = expr comparisons. At the moment, only key = column or key = constant comparisons are optimized.

• Join some of the copy functions for nicer code.

• Change sql_yacc.yy to an inline parser to reduce its size and get better error messages.

• Change the parser to use only one rule per different number of arguments in function.

• Use of full calculation names in the order part (for Access97).

• MINUS , INTERSECT , and FULL OUTER JOIN . (Currently UNION and LEFTRIGHT OUTER JOIN are supported.)

• Allow SQL_OPTION MAX_SELECT_TIME= val , for placing a time limit on a query.

• Allow updates to be logged to a database.

• Enhance LIMIT to allow retrieval of data from the end of a result set.

• Alarm around client connect/read/write functions.

• Please note the changes to mysqld_safe : According to FSSTND (which Debian tries to follow), PID files should go into /var/run/<progname>.pid and log files into /var/log . It would be nice if you could put the "DATADIR" in the first declaration of "pidfile" and "log" so that the placement of these files can be changed with a single statement.

• Allow a client to request logging.

• Allow the LOAD DATA INFILE statement to read files that have been compressed with gzip .

• Fix sorting and grouping of BLOB columns (partly solved now).

• Change to use semaphores when counting threads. One should first implement a semaphore library for MIT-pthreads.

• Add full support for JOIN with parentheses.

• As an alternative to the one-thread-per-connection model, manage a pool of threads to handle queries.

• Allow GET_LOCK() to obtain more than one lock. When doing this, it is also necessary to handle the possible deadlocks this change will introduce.

1.6.6 New Features We Don't Plan to Implement

We aim toward full compliance with ANSI/ISO SQL. There are no features we plan not to implement.