MySQL is a powerful, open source relational database management system. However, users may not be familiar with the full extent of all of its capabilities. In this tip, SearchEnterpriseLinux.com's MySQL core-certified expert, Scott Noyes, defines ACID-compliant transactions, blob fields and buffer pools and the functions of mulitple storage engines in MySQL.
What are ACID-compliant transactions? Does MySQL support them?
Scott Noyes: ACID stands for Atomicity, Consistency, Isolation and Durability. In a database server with many simultaneous connections, problems might arise if some process changes data between the time an application has searched for it and tries to act on it. ACID-compliant transactions ensure that processes cannot interfere with each other, and that data remains in a complete state.
MySQL offers ACID-compliant transactions with the InnoDB engine, and the upcoming Falcon engine. See http://dev.mysql.com/books/mysqlpress/mysql-tutorial/ch10.html.
Can you explain how buffer pools relate to MySQL and databases, in general?
Noyes: Buffer pools are memory data caches. Reading data off a disk is slow, but reading data from memory is much faster. The first query to request a row of data must wait for the response from the disk. After that, the data is stored in the buffer pool, and other queries requesting that same row get a faster response.
Obviously, the cache can only hold a certain amount of data, so decisions must be made as to how much memory to allocate to the buffer pool, which rows to keep in the buffer pool and how to keep the cache and the disk in sync when the data changes. Read about InnoDB's use of buffer pools and how to configure for optimal performance at http://dev.mysql.com/doc/refman/5.0/en/innodb-disk-io.html and http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html.
What is the equivalent of a blob field in MySQL?
Noyes: MySQL offers four blob types, which differ only in maximum size. They are tinyblob (256 bytes), blob (64 kilobytes), mediumblob (16 megabytes) and longblob (4 gigabytes). There are also the binary and varbinary data types, which are the same as char ard varchar, but store binary strings. See http://dev.mysql.com/doc/refman/5.0/en/blob.html and http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html for more details.
How do you combine two fields to create a third? For example, field1: abcd and field2: 1111need to be combined to get field3: abcd111.
Noyes: Use:
UPDATE table SET field3 = CONCAT(field1, field2)
Check out this list of string functions. This concatenation can be done automatically by adding BEFORE triggers on INSERT and UPDATE.
Within the trigger, set
NEW.field3 = CONCAT(NEW.field1, NEW.field2)
You can find out more about triggers in MySQL at: http://dev.mysql.com/doc/refman/5.0/en/triggers.html.
How do multiple storage engines work in MySQL?
Noyes: The multiple storage engine architecture allows applications to take advantage of the particular strengths of each engine, allowing for much finer grain control. Within a single database, you might have some tables using MyISAM for its speed, small memory footprint and fulltext capabilities, while others use InnoDB for its support of transactions and foreign keys. You can even roll your own storage engine, if you want to use MySQL as a front end to your proprietary data format.
