Charles Engelke's Blog

July 9, 2003

OSCON Day 2: New Features in MySQL

Filed under: Ozette Brown — Charles Engelke @ 4:26 pm

Here’s a summary of this talk I grabbed from Jeremy Zawodny. Jeremy is well respected in the MySQL
community. He’s also the chief Yahoo at Yahoo Finance, although I hear that he’s has another job.
This summary hits the major points made.

I’d like to add that MySQL cross database queries and multi-table deletes are excellent. Also,
Query Caching has more than paid for itself. This by far is one of their best features.

Replication will be the next feature we implement. I’ve been using it in test environments is various modes;
master-to-master and master-to-slave. Replication appears to be amazingly fast. Monty (the co-founder of MySQL and
chief engineer) says that version 4.1 should be available in a few weeks. The some of the features of 4.1
are further below.

Here’s Jeremy’s stuff: David Axmark is presenting MySQL’s new features, covering 4.0, 4.1, and a
bit of 5.0. I’m at this presentation mostly because I’ve been using
the new features that I no longer consider them new. That means I
have trouble enumerating the new features when people ask. So I’m
hoping this talk provides a nice summary that I can reuse.

SAP Deal

SAP will use MySQL as their default database in a few years. MySQL
is providing ideas for implementation, advice, access to developers,
and so on.

MySQL 4.0

  • Multi-table Deletes
  • Dynamic Server Variables
  • Query Cache
  • Handler Interface (for migrating old ISAM style code)
  • SQL_CALC_FOUND_ROWS
  • Faster SELECT COUNT(DISTINCT)
  • TRUNCATE TABLE
  • Multi-threaded Index Rebuilds
  • Boyer-More Searches for LIKE Comparisons
  • Split Index and Data Directories in CREATE TABLE
  • Multi-table DELETEs
  • Improved Full-Text searching (boolean as of 4.0.3)

MySQL 4.1

  • On-line help
  • SSL in the client server protocol
  • Multi-table UPDATEs
  • Character sets and collations per column/table/db/server (lots of
    slides on this topic)
  • CONVERT() can be used to convert between character sets
  • Multi-threaded replication
  • Prepared statements
  • Sub-queries (nested SELECTs)
  • COMPRESS()/UNCOMPRESS() using zlib on the server (in 4.1.1)
  • Geometric data types (2-D: line, point, shapes, etc.)
  • Pre-loading of MyISAM index data
  • Multiple statements in a single command
  • Secure replication
  • Query timeouts (or time limits)

MySQL 5.0

  • Foreign keys (referential integrity) for MyISAM
  • On-line backup for MyISAM (easy replication sync too)
  • BIT column
  • True VARCHAR columns (no space trimming)
  • ARRAY column
  • Warning/Info system (better alerting and feedback)
  • Stored Procedures
  • Multiple key caches (you ping cables to a particular cache)
Advertisements

Create a free website or blog at WordPress.com.

%d bloggers like this: