New features in the SQL for the COMMING Future Articles, samples and tutorials

1.6.3 New Features Planned for the Near Future
New 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:
Chapter 1: General Information 29
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
² 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
30 MySQL Technical Reference for Version 5.0.1-alpha
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-tostring-
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.

Online Members

New features in the SQL for the COMMING Future

Post Comment:

Email subscription