As your SQL business logic becomes more complex, you might find yourself repeatedly writing blocks of SQL statements to perform the same database operation at the application level—for example, inserting a set of linked records or performing calculations on a particular result set. In these situations, it usually makes sense to turn this block of SQL code into a reusable routine, which resides on the database server (rather than in the application) so that it can be managed independently and invoked as needed from different modules in your application.

Packaging SQL statements into server-side routines has four important advantages.

• A stored routine is held on the database server, rather than in the application. For applications based on a client-server architecture, calling a stored routine is faster and requires less network bandwidth than transmitting an entire series of SQL statements and taking decisions on the result sets. Stored routines also reduce code duplication by allowing developers to extract commonly used SQL operations into a single component. The end result is that application code becomes smaller, more efficient, and easier to read.

• A stored routine is created once but used many times, often from more than one program. If the routine changes, the changes are implemented in one spot (the routine definition) while the routine invocations remain untouched. This fact can significantly simplify code maintenance and upgrades. Debugging and testing an application also becomes easier, as errors can be traced and corrected with minimal impact to the application code.

• Implementing database operations as stored routines can improve application security, because application modules can be denied access to particular tables and only granted access to the routines that manipulate those tables. This not only ensures that an application only sees the data it needs, but also ensures consistent implementation of specific tasks or submodules across the application (because all application modules will make use of the same stored routines rather than attempting to directly manipulate the base tables).

• Using stored routines encourages abstract thinking, because packaging SQL operations into a stored routine is nothing more or less than understanding how a specific task may be encapsulated into a generic component. In this sense, using stored routines encourages the creation of more robust and extensible application architecture.

It’s worth noting also that in the MySQL world, the term “stored routines” is used generically to refer to two different animals: stored procedures and stored functions. While both types of routines contain SQL statements, MySQL imposes several key restrictions on stored functions that are not applicable to stored procedures, as follows:

• Stored functions cannot use SQL statements that return result sets.

• Stored functions cannot use SQL statements that perform transactional commits or rollbacks.

• Stored functions cannot call themselves recursively.

• Stored functions must produce a return value.

Source of Information : McGraw Hill - MySQL Database Usage and Administration

• 0 comments »

So far, you’ve seen transactions in the context of InnoDB tables, the only native MySQL storage engine to support ACID-compliant transactions. The older MySQL table types, still in use in many MySQL installations, do not support transactions, but MySQL still enables users to implement a primitive form of transactions through the use of table locks. This section examines these “pseudo-transactions” in greater detail, with a view to offering some general guidelines on performing secure transactions with nontransactional tables.

MySQL supports a number of different table types, and the locking mechanisms available differ from type to type. Therefore, a clear understanding of the different levels of locking available is essential to implementing a pseudo-transaction environment with MySQL’s nontransactional tables.

• Table locks. The entire table is locked by a client for a particular kind of access. Depending on the type of lock, other clients will not be allowed to insert records into the table, and could even be restricted from reading data from it.

• Page locks. MySQL will lock a certain number of rows (called a page) from the table. The locked rows are only available to the thread initiating the lock. If another thread wants to write to data in these rows, it must wait until the lock is released. Rows in other pages, however, remain available for use.

• Row locks. Row-level locks offer finer control over the locking process than either table-level locks or page-level locks. In this case, only the rows that are being used by the thread are locked. All other rows in the table are available to other threads. In multiuser environments, row-level locking reduces conflicts between threads, making it possible for multiple users to read and even write to the same table simultaneously. This flexibility must be balanced, however, against the fact that it also has the highest performance overhead of the three locking levels.

The MyISAM table type supports only table-level locking, which offers performance benefits over row- and page-level locking in situations involving a larger number of reads than writes. The InnoDB table type automatically performs row-level locking in transactions.

Source of Information : McGraw Hill - MySQL Database Usage and Administration

• 0 comments »

One of the most important properties of a transaction-capable RDBMS is its capability to “isolate” the different sessions in progress at any given instance on the server. In a single-user environment, this property is largely irrelevant for obvious reasons: There is nothing to isolate because usually only a single session is active at any time. In more complex real-world scenarios, however, it is unlikely this assumption will remain true.

In a multiuser environment, many RDBMS sessions will usually be active at any given time. In the stock trading example discussed previously, for instance, it is unlikely that only a single trade will be taking place at a particular point in time. Far more likely is that hundreds of trades will occur simultaneously. In such a situation, it is essential that the RDBMS isolate transactions so that they do not interfere with each other, while simultaneously ensuring the database’s performance does not suffer as a result.

To understand the importance of isolation, consider what would happen if it wasn’t enforced. In the absence of transaction isolation, different SELECT statements would retrieve different results within the context of the same transaction because the underlying data was modified by other transactions in the interim. This would create inconsistency and make it difficult to trust a particular result set or use it as the basis for calculations with any degree of confidence. Isolation thus imposes a degree of insulation between transactions, guaranteeing that an application only sees consistent data within the scope of a transaction. MySQL provides the following four isolation levels in accordance with the ANSI/ISO SQL specification:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

These transaction isolation levels determine the degree to which other transactions can “see” inside an in-progress transaction, and are arranged in hierarchical order, beginning with the least secure (and most problematic) level and gradually moving to the most secure level. These isolation levels can be manipulated with the TRANSACTION ISOLATION LEVEL variable, which is discussed in greater detail in the section “Modifying the Transaction Isolation Level.” Let’s now look at what each of the isolation levels does.



The READ UNCOMMITTED Isolation Level
The READ UNCOMMITTED isolation level provides the minimum amount of insulation between transactions. In addition to being vulnerable to phantom reads and unrepeatable reads, a transaction at this isolation level can read data that has not yet been committed by other transactions. If this transaction now uses the uncommitted changes made by other transactions as the basis for calculations of its own, and those uncommitted changes are then rolled back by their parent transactions, it can result in massive data corruption.



The READ COMMITTED Isolation Level
Even less secure than the REPEATABLE READ isolation level is the READ COMMITTED isolation level. At this level, a transaction can see the committed changes of other transactions during its lifetime. Put another way, this means multiple SELECT statements within the same transaction might return different results if the corresponding tables have been modified by other transactions in the intervening period.

In this case, the second transaction will continue to see zero records during the lifetime of the first transaction. However, once the first transaction commits its changes, the second one will see one flight, even though it is still in progress. This is obviously a problem—if the second transaction sees two different results for the same operation, it isn’t going to know which one to trust as the correct one. Extrapolate a little and assume that instead of a single transaction, many transactions are committing updates to the database, and you’ll see every query executed by a transaction could produce a different result set (hence, the term “unrepeatable read” for this kind of situation).



The REPEATABLE READ Isolation Level
For applications that are willing to compromise a little on security for better performance, MySQL offers the REPEATABLE READ isolation level. At this level, a transaction will not see the changes carried out by concurrent transactions until it itself has concluded.

In this case, the second transaction can see the new flight added by the first transaction only once both transactions are complete. This is, in fact, the way most users expect transactions to work, and it should come as no surprise that this is MySQL’s default transaction isolation level. The InnoDB storage engine accomplishes this by using multiversioning to store a snapshot of the query results when the query is executed for the first time; it then reuses this snapshot for all subsequent queries until the transaction is committed.



The SERIALIZABLE Isolation Level
This SERIALIZABLE isolation level offers the maximum amount of insulation between transactions by treating concurrent transactions as though they were executing sequentially, one after the other.

Here, the first transaction is adding a new flight to the database, while the second is attempting to view the total number of flights. However, because MySQL is executing these transactions serially, the INSERT operation in the first transaction will lock the table until the transaction is complete. This will force the SELECT operation in the second transaction to wait until the lock is released before it can obtain a result. This “serialized” approach to handling transactions is the most secure: Sequentially locking and unlocking the table ensures that each transaction only sees data that has actually been committed to the database, with no possibility of dirty or unrepeatable reads. However, this comes at a price: MySQL will take a performance hit if every transaction runs at this isolation level because of the large amount of resources required to handle the various transactional locks at any given instant.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

MySQL fully satisfies the ACID requirements for a transaction-safe RDBMS, as follows:

• Atomicity is handled by storing the results of transactional statements (the modified rows) in a memory buffer and writing these results to disk and to the binary log from the buffer only once the transaction is committed. This ensures that the statements in a transaction operate as an indivisible unit and that their effects are seen collectively, or not at all.

• Consistency is primarily handled by MySQL’s logging mechanisms, which record all changes to the database and provide an audit trail for transaction recovery. In addition to the logging process, MySQL provides locking mechanisms that ensure that all of the tables, rows, and indexes that make up the transaction are locked by the initiating process long enough to either commit the transaction or roll it back.

• Server-side semaphore variables and locking mechanisms act as traffic managers to help programs manage their own isolation mechanisms. For example, MySQL’s InnoDB engine uses fine-grained row-level locking for this purpose.

• MySQL implements durability by maintaining a binary transaction log file that tracks changes to the system during the course of a transaction. In the event of a hardware failure or abrupt system shutdown, recovering lost data is a relatively straightforward task by using the last backup in combination with the log when the system restarts. By default, InnoDB tables are 100 percent durable (in other words, all transactions committed to the system before the crash are liable to be rolled back during the recovery process), while MyISAM tables offer partial durability.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

The MySQL transaction architecture fully satisfies the ACID tests for transaction safety via its InnoDB storage engine. Older table types, such as the MyISAM type, do not support transactions. Transactions in such systems, therefore, can only be implemented through the use of explicit table locks (although this may not be ACID-compliant). The term “ACID” is an acronym, stating four properties that every transactional RDBMS must comply with. To qualify for ACID compliance, an RDBMS must exhibit the following characteristics, as described in the following sections.


Atomicity
Atomicity means that every transaction must be treated as an indivisible unit. Given a transaction consisting of two or more tasks, all the statements within it must be successful for the transaction to be considered successful. In the event of a transaction failure, the system should be returned to its pre-transaction state.

With reference to the previous stock exchange example, atomicity means the sale of shares by Trader A and the purchase of the same by Trader B cannot occur independently of each other, and both must take place for the transaction to be considered complete. Similarly, in the airline example, atomicity implies that it would not be possible for the system to add a flight without also adding corresponding departure timings and class/seat information.

For a transaction to meet the atomicity requirement, if any of the statements in the transaction fail, all of the preceding statements must be rolled back to ensure the integrity of the database is unaffected. This is particularly important in mission-critical, real-world applications (like financial systems) that perform data entry or updates and require a high degree of safety from undetected data loss.


Consistency
Consistency means that every transaction must ensure that the database is in a consistent state once it completes executing. Or, to put it another way, consistency means that the database must never reflect a partially completed transaction at any time.

With reference to the previous stock exchange example, consistency means that every debit from a seller’s account results in a corresponding and equal credit to a buyer’s account. If a transaction reduces Trader A’s account by 400 shares, but only credits 300 shares to Trader B’s account, the consistency constraint will be violated because the total number of shares in the system changes. Similarly, the consistency property would ensure that if a flight is removed, all data related to that flight, including departure timings and seat/class information, would also be removed.


Isolation
Isolation means that every transaction must occur in its own separate and independent “transaction space,” and its impact on the database only becomes visible once the transaction has completed executing (regardless of whether the transaction was successful or not). This is particularly important in multiuser, multitransaction systems, because it implies that the effects of a particular transaction are not “felt” until the transaction is complete. In the absence of the isolation property, two conflicting transactions might quickly produce data corruption, because each transaction would violate the other’s integrity. With reference to the previous stock exchange example, for instance, isolation implies the transaction between the two traders is independent of all other transactions on the exchange and its result is visible to the public at large only once it has been completed.

When considering a flight modification, it implies that the list of available flights is updated only once the transaction is complete, and does not reflect other transactions that might still be in process at any given instant.

In reality, of course, the only way to obtain absolute isolation is to ensure that only a single user can access the database at any time. This is not a practical solution at all when dealing with a multiuser RDBMS like MySQL. Instead, most transactional systems use either page-level locking or row-level locking to isolate the changes made by different transactions from each other, at some cost in performance.


Durability
Durability means that changes made by a successful transaction will not be lost, even if the system crashes. Most RDBMS products ensure data durability by keeping a log of all activity that alters data in the database in any way. This database log keeps track of any and all updates made to tables, queries, reports, and so on.

In the event of a system crash or a corruption of the data storage media, the system is able to recover to the last successful update on restart and reflect the changes carried out by transactions that were still in progress when it went down through the use of its logs. In the context of the previous share transfer example, durability means that once the transfer of shares from Trader A to Trader B has completed successfully, the system should reflect that state, even if a system failure subsequently takes place. Or, when dealing with the airline database, flights that have been added should not vanish from the database in the event of a system failure.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

MySQL supports many different storage engines for its tables, each with its own advantages and disadvantages. While all of MySQL’s storage engines are reasonably efficient, using the wrong storage engine can hinder your application from achieving its maximum possible performance. For example, using the ARCHIVE engine for a table that will see frequent reads and writes will produce significantly slower performance than using the MYISAM engine for the same table. To help you choose the most appropriate engine for your table, the following sections discuss each of these engines in greater detail.



The MyISAM Storage Engine
The MyISAM storage engine extends the base ISAM type with a number of additional optimizations and enhancements, and is MySQL’s default table type. MyISAM tables are optimized for compression and speed, and are immediately portable between different OSs and platforms (for example, the same MyISAM table can be used on both Windows and UNIX OSs). The MyISAM format supports large table files (up to 256TB in size) and allows indexing of BLOB and TEXT columns. Tables and table indexes can be compressed to save space, a feature that comes in handy when storing large BLOB or TEXT fields. VARCHAR fields can either be constrained to a specific length or adjusted dynamically as per the data within them, and the format supports searching for records using any key prefix, as well as using the entire key.

Because MyISAM tables are optimized for MySQL, it’s no surprise that the developers added a fair amount of intelligence to them. MyISAM tables can be either fixed-length or dynamic-length. MySQL automatically checks MyISAM tables for corruption on startup and can even repair them in case of errors. Table data and table index files can be stored in different locations, or even on different file systems. And intelligent defragmentation logic ensures a high-performance coefficient, even for tables with a large number of inserts, updates, and deletions. Large MyISAM tables can also be compressed, or “packed,” into smaller read-only tables that take up less disk space, with MySQL’s myisampack utility.



The InnoDB Storage Engine
The InnoDB storage engine has been a part of MySQL since MySQL 4.0. InnoDB is a fully ACID-compliant and efficient table format that provides full support for transactions in MySQL without compromising speed or performance. Fine-grained (row- and table-level) locks improve the fidelity of MySQL transactions, and InnoDB also supports nonlocking reads and multiversioning (features previously only available in the Oracle RDBMS). InnoDB tables can grow up to 64TB in size.

Asynchronous I/O and a sequential read-ahead buffer improve data retrieval speed, and a “buddy algorithm” and Oracle-type tablespaces result in optimized file and memory management. InnoDB also supports automatic creation of hash indexes in memory on an as-needed basis to improve performance, and it uses buffering to improve the reliability and speed of database operations. As a result, InnoDB tables match (and, sometimes, exceed) the performance of MyISAM tables. InnoDB tables are fully portable between different OSs and architectures, and, because of their transactional nature, they’re always in a consistent state (MySQL makes them even more robust by checking them for corruption and repairing them on startup). Support for foreign keys and commit, rollback, and roll-forward operations complete the picture, making this one of the most full featured table formats available in MySQL.



The Archive Storage Engine
The Archive storage engine provides a way to store large recordsets that see infrequent reads into a smaller, compressed format. The key feature of this storage engine is its ability to compress records as they are inserted and decompress them as they are retrieved using the zlib library. These tables are ideally suited for storage of historical data, typically to meet auditing or compliance norms.

Given that this storage engine is not designed for frequent reads, it lacks many of the bells and whistles of the InnoDB and MyISAM engines: Archive tables only support INSERT and SELECT operations, do not allow indexes (and, therefore, perform full table scans during reads), ignore BLOB fields in read operations, and, by virtue of their on-the-fly compression system, necessarily display lower performance. That said, Archive tables are still superior to packed MyISAM tables because they support both read and write operations and produce a smaller disk footprint.



The Federated Storage Engine
The Federated storage engine implements a “stub” table that merely contains a table definition; this table definition is mirrored on a remote MySQL server, which also holds the table data. A Federated table itself contains no data; rather, it is accompanied by connection parameters that tell MySQL where to look for the actual table records. Federated tables thus make it possible to access MySQL tables on a remote server from a local server without the need for replication or clustering.

Federated “stub” tables can point to source tables that use any of MySQL’s standard storage engines, including InnoDB and MyISAM. However, in and of themselves, they are fairly limited; they lack transactional support and indexes, cannot use MySQL’s query cache, and are less than impressive performance-wise.



The Memory Storage Engine
The Memory storage engine, as the name suggests, implements in-memory tables that use hash indexes, making them at least 30 percent faster than regular MyISAM tables. They are accessed and used in exactly the same manner as regular MyISAM or ISAM tables. However, the data stored within them is available only for the lifetime of the MySQL server and is erased if the MySQL server crashes or shuts down. Although these tables can offer a performance benefit, their temporary nature makes them unsuitable for uses more sophisticated than temporary data storage and management.



The CSV Storage Engine
The CSV storage engine provides a convenient way to merge the portability of text files with the power of SQL queries. CSV tables are essentially plain ASCII files, with commas separating each field of a record. This format is easily understood by non-SQL applications, such as Microsoft Excel, and thus allows data to be easily transferred between SQL and non-SQL environments. A fairly obvious limitation, however, is that CSV tables don’t support indexing and SELECT operations must, therefore, perform a full table scan, with the attendant impact on performance. CSV tables also don’t support the NULL data type.



The MERGE Storage Engine
A MERGE table is a virtual table created by combining multiple MyISAM tables into a single table. Such a combination of tables is only possible if the tables involved have completely identical table structures. Any difference in field types or indexes won’t permit a successful union. A MERGE table uses the indexes of its component tables and doesn’t maintain any indexes of its own, which can improve its speed in certain situations. MERGE tables permit SELECT, DELETE, and UPDATE operations, and can come in handy when you need to pull together data from different tables or to speed up performance in joins or searches between a series of tables.



The ISAM Storage Engine
ISAM tables are similar to MyISAM tables, although they lack many of the performance enhancements of the MyISAM format and, therefore, don’t offer the optimization and performance efficiency of that type. Because ISAM indexes cannot be compressed, they use fewer system resources than their MyISAM counterparts. ISAM indexes also require more disk space, however, which can be a problem in small-footprint environments.

Like MyISAM, ISAM tables can be either fixed-length or dynamic-length, though maximum key lengths are smaller with the ISAM format. The format cannot handle tables greater than 4GB, and the tables aren’t immediately portable across different platforms. In addition, the ISAM table format is more prone to fragmentation, which can reduce query speed, and has limited support for data/index compression.



The NDB Storage Engine
The NDB storage engine implements a high-availability, in-memory table type designed only for use in clustered MySQL server environments. The NDB format supports large table files (up to 384EB in size), variable-length fields, and replication. However, NDB tables don’t support foreign keys, savepoints, or statement-based replication, and limit the number of fields and indexes per table to 128.



Storage Engine Selection Checklist
To decide the most appropriate storage engine for a table, take into account the following factors:
• Frequency of reads versus writes
• Whether transactional support is needed
• Whether foreign key support is needed
• Indexing requirements
• Table size and speed at which it will grow
• OS/architecture portability
• Future extendibility requirements and adaptability to changing data requirements

It’s worth noting, also, that MySQL lets you mix and match storage engines within a database. So you could use the MyISAM engine for tables that see frequent SELECTs and use InnoDB tables for tables that see frequent INSERTs or transactions. This ability to select storage engines on a per-table basis is unique to MySQL and plays a key role in helping it achieve its blazing performance.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

Every field of a MySQL table incorporates a data type as one of its primary attributes. This data type plays an important role in enforcing the integrity of the data in a MySQL database and in making this data easier to use and manipulate.

Intelligent use of data typing can result in smaller databases and tables, efficient indexing, and quicker query execution; indifferent, ham-handed use of types can result in bloated tables, wasted storage space, inefficient indexing, and a gradual deterioration in performance. For example, using a VARCHAR type on a field that is meant for numeric or date values could result in unexpected behavior when you perform calculations on it, just as using a large TEXT field for small string values could lead to a waste of space and inefficient indexing. Wise database architects, therefore, make it a point to be fully aware of the various data types available in a system, together with the limitations and benefits of each, prior to implementing a database-driven application; the alternative can be costly in terms of both time and money.



Numeric Types
For integer values, MySQL offers you a choice of the TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT types, which differ from each other only in the size of values they can store. Use the TINYINT and SMALLINT types for small integer values, the INT type for larger integer values, and the BIGINT type for extremely large values. For floatingpoint values, use the FLOAT and DOUBLE types for single-precision and double-precision floating point values, respectively. And, finally, for decimal values, use the DECIMAL data type.

When defining an integer field, you can include a width specifier in parentheses. This width specifier controls the padding MySQL applies to the field when retrieving it from the database. For a field defined as BIGINT (20), MySQL will automatically pad the value to 20 characters before displaying it.

When defining floating-point and decimal fields, MySQL enables you to include both a width specifier and a precision specifier. For example, the declaration FLOAT (7,4) specifies that displayed values will not contain more than seven digits, with four digits after the decimal point. You can also add the ZEROFILL attribute to pad values with leading zeroes, and the UNSIGNED attribute to force a field to only accept positive values.



Character and String Types
MySQL lets you store strings up to 255 characters in length as either a CHAR or VARCHAR type. The difference between these two types is simple: CHAR fields are fixed to the length specified at the time of definition, while VARCHAR fields can grow and shrink dynamically, based on the data entered into them. This makes VARCHAR fields more suitable for fields that accept variable-length data, and CHAR fields better for fields that always contain values of the same length. Both CHAR and VARCHAR type definitions must include a width specifier in parentheses, as with numeric type definitions. Thus, the definition CHAR (10) creates a field whose length remains exactly 10 characters, regardless of what is entered into it, while the definition VARCHAR (10) creates a field whose length can range anywhere between 0 and 10 characters, depending on what is entered into it.



Text and Binary Types
MySQL enables you to store strings greater than 255 characters in length as either a TEXT or BLOB type. The difference between TEXT and BLOB types is minimal at best:
TEXT types are compared in a case-insensitive manner, while BLOB types are compared in a case-sensitive manner. For this reason, BLOBs are usually used to store binary data, while TEXT fields are used to store ASCII data. Depending on the size of the string you’re trying to store, MySQL offers you a choice of the TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT types (for ASCII text blocks) and the TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB types (for binary data).



Date and Time Types
For simple date and time values, MySQL offers the intelligently named DATE and TIME data types. The DATE type is used to store date values consisting of year, month, and day components, while the TIME type is used for time values or durations consisting of hour, minute, and second components. Both DATE and TIME types can be used for values in either numeric (YYYYMMDD and HHMMSS) or string (‘YYYY-MM-DD’ and ‘HH:MM:SS’) format.

If what you need is a combination of the two, consider using the DATETIME or TIMESTAMP types, both of which let you specify both date and time values in a single field. The difference between the two lies in how the values are stored: DATETIME fields are stored in the form ‘YYYY-MM-DD HH:MM:SS’, and TIMESTAMP fields are stored in the form YYYYMMDDHHMMSS.

Finally, for simple applications that only need to store the year, MySQL offers the special YEAR type, which accepts a four-digit year value. It’s worthwhile to use this value if your application deals mostly with the year component of a date value, because a field marked as YEAR occupies 1 byte on disk (as compared to a DATETIME or DATE field, which can occupy up to 8 bytes). MySQL YEAR fields can accept any value in the range 1901 to 2155.

When inserting records into a table containing a TIMESTAMP field, MySQL automatically fills that field with the current date and time if no other value was specified. To accomplish the same thing with other date/time fields, use the NOW() function.



Enumerations
For situations where a field value must be selected from a predefined list of values, MySQL offers the ENUM and SET data types. For both these types, a list of predefined values must be included as part of the type definition. An ENUM field definition can contain up to 65,536 elements, while a SET field definition can hold up to 64 elements. For a field marked as an ENUM field, only one of the predefined values may be selected, whereas for a field marked as a SET field, zero, one, or more than one of the pre-defined values may be selected. ENUM fields are best suited for mutually exclusive values, while SET fields are best suited for independent values. As an example, the definition ENUM (‘red’, ‘green’, ‘yellow’) forces entry of any one of the three values, while the definition SET (‘mon’, ‘tue’, ‘wed’, ‘thu’, ‘fri’) allows entry of none, one, or all of the five values. In addition, SET values are stored as bits, making it possible to perform bitwise comparison and sorting operations on them.



Data Type Selection Checklist
To decide the data type for a field, take into account the following factors:
• The range and type of values that the field will hold
• The types of calculations you expect to perform on those values
• The manner in which the data is to be formatted for display purposes
• The manner in which the data is to be sorted and compared against other fields
• The available subtypes for each field and their storage efficiencies

By taking all of these factors into consideration when designing your database, you reduce the chance of incompatibilities and storage inefficiencies.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

MySQL also comes with a full-featured list of SHOW statements to obtain information about all aspects of the server, its databases, and its tables. Here’s a quick list:

• The SHOW DATABASES statement displays a list of databases on the server.

• The SHOW TABLES statement displays a list of tables in a database.

• The DESCRIBE statement displays the structure of a table.

• The SHOW CREATE TABLE statement retrieves the SQL statements originally used to create the table.

• The SHOW INDEX statement displays a list of table indexes.

• The SHOW ENGINES statement retrieves a list of available storage engines.

• The SHOW PROCESSLIST statement displays a list of active connections to the server, as well as what each one is doing.

• The SHOW ERRORS and SHOW WARNINGS statements display a list of errors and warnings generated by the server.

• The SHOW STATUS statement displays live server status (including information on server uptime, number of queries processed, and number of connections).

• The SHOW TABLE STATUS statement displays detailed information on the tables in a database (including information on the table type, the number of rows, the date and time of the last table update, and the lengths of indexes and rows).

• The SHOW CHARACTER SET statement displays a list of available character sets.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

A number of other keywords can be added to the SELECT statement to modify its behavior.

• The SQL_CACHE and SQL_NO_CACHE keywords tell MySQL whether the query results should be cached.

• The SQL_BUFFER_RESULT keyword forces MySQL to store query results in a temporary table. This result buffer eliminates the need for MySQL to lock the tables used by the query while the results are being transmitted to the client, thus ensuring they can be used by other processes in the interim.

• The SQL_BIG_RESULT and SQL_SMALL_RESULT keywords can be used to indicate the expected size of the result set to MySQL and, thereby, help it identify the most optimal way to sort and store the returned records (disk-based or inmemory temporary tables, respectively).

• The SQL_HIGH_PRIORITY keyword raises the priority of the query over competing UPDATE, INSERT, or DELETE statements, thereby resulting in (slightly) faster query execution on busy database servers.

• The SQL_CALC_FOUND_ROWS keyword tells MySQL to calculate the total number of rows matching the query, without taking into account any LIMIT that might have been set. This total number can then be retrieved via a call to the FOUND_ROWS()function.

Appropriate usage of the SQL_CACHE, SQL_BUFFER_RESULT, SQL_BIG_RESULT, SQL_SMALL_RESULT, and SQL_HIGH_PRIORITY keywords can significantly improve the speed of your transactions with the MySQL server.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

The TYPE attribute isn’t the only option available to control the behavior of the table being created. A number of other MySQL-specific attributes are also available. Here’s a list of the more interesting ones.

• The AUTO_INCREMENT modifier specifies the starting value to use for AUTO_ INCREMENT fields in the table.

• The CHARACTER SET and COLLATE modifiers specify the table character set and collation.

• The CHECKSUM modifier controls whether table checksums should be calculated and stored.

• The COMMENT modifier saves a descriptive label for the table.

• The MAX_ROWS and MIN_ROWS modifiers specify the maximum and minimum number of rows the table is likely to have.

• The PACK_KEYS modifier controls whether table indexes are compressed. Compressing indexes reduces the table’s size on disk, but can affect performance (as indexes need to be uncompressed every time they are updated).

• The DELAY_KEY_WRITE modifier controls whether table indexes are updated only after all writes to the table are complete. This can improve performance for tables that see a high frequency of writes.

• The UNION modifier specifies a list of tables to be merged (only useful with the
MERGE storage engine).

• The DATA DIRECTORY and INDEX DIRECTORY modifiers specify custom paths for the table data and index files.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

Following the field definitions and modifiers come one or more table modifiers, which specify table-level attributes. Of these, the most frequently used one is the ENGINE modifier, which tells MySQL which storage engine, or table type, to use. A number of such engines are available, each with different advantages.

MySQL Storage Engines
ISAM : Legacy engine
MYISAM : Revision of ISAM engine with support for dynamic-length fields
INNODB : ACID-compliant transactional engine with support for foreign keys
MEMORY : Memory-based engine with support for hash indexes
CSV : Text-based engine for CSV recordsets
ARCHIVE : Engine with compression features for large recordsets
FEDERATED : Engine for remote tables
NDB : Engine for clustered tables
MERGE : Engine for merged tables
BLACKHOLE : Bitbucket engine

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

A number of additional constraints, or modifiers, can be applied to a field to increase the consistency of the data that will be entered into it and to mark it as “special” in some way. These modifiers can either appear as part of the field definition, if they apply only to that specific field (for example, a default value for a field), or after all the field definitions, if they relate to multiple fields (for example, a multicolumn primary key).

• To specify whether the field is allowed to be empty or if it must necessarily be filled with data, place the NULL and NOT NULL modifiers after each field definition.

• To specify a default value for a field, use the DEFAULT modifier. This default value is used if no value is specified for that field when inserting a record. In the absence of a DEFAULT modifier for NOT NULL fields, MySQL automatically inserts a nonthreatening default value into the field.

• To have MySQL automatically generate a number for a field (by incrementing the previous value by 1), use the AUTO_INCREMENT modifier. This is particularly useful to generate row numbers for each record in the table. However, the AUTO_INCREMENT modifier can only be applied to numeric fields that are both NOT NULL and belong to the PRIMARY KEY. A table may only contain one AUTO_INCREMENT field.

• To specify the character set for fields containing string values, use the CHARACTER SET modifier.

• To index a field, use the INDEX modifier. When a field is indexed in this manner, MySQL no longer needs to scan each row of the table for a match when performing queries; instead, it can simply look up the index. Indexing is recommended for fields that frequently appear in the WHERE, ORDER BY, and GROUP BY clauses of SELECT queries and for fields used to join tables together.

• A variant of the INDEX modifier is the UNIQUE modifier, which is a special type of index used to ensure that values entered into a field must be either unique or NULL.

• To specify a primary key for the table, use the PRIMARY KEY modifier. The PRIMARY KEY constraint can best be thought of as a combination of the NOT NULL and UNIQUE constraints because it requires values in the specified field to be neither NULL nor repeated in any other row. It thus serves as a unique identifier for each record in the table, and it should be selected only after careful thought has been given to the inter-relationships between tables.

• To specify a foreign key for a table, use the FOREIGN KEY modifier. The FOREIGN
KEY modifier links a field in one table to a field (usually a primary key) in another table, setting up a base for relationships. However, foreign keys are only supported in MySQL’s InnoDB storage engine; the FOREIGN KEY modifier is simply ignored in all other engines.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

When creating a MySQL table, specifying a data type for every field is necessary. This data type plays an important role in enforcing the integrity of the data in a MySQL database and in making this data easier to use and manipulate. MySQL offers a number of different data types, which are summarized below.

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT : Integer values
FLOAT : Single-precision floating-point values
DOUBLE : Double-precision floating-point values
DECIMAL : Decimal values
BIT : Bit-field values
CHAR : Fixed-length strings up to 255 characters
VARCHAR : Variable-length strings up to 255 characters
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB : Binary data
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT : Text blocks
DATE : Date values
TIME : Time values or durations
YEAR : Year values
DATETIME,TIMESTAMP : Combined date and time values
ENUM,SET : Predefined sets of values

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

MySQL’s technical architecture, built as it is around the three tenets of performance,
reliability, and ease of use, have made the product extremely popular, both on and off the Web. According to the MySQL website, more than 100 million copies of MySQL have been downloaded and distributed to date, and 50,000 more are added to that total every day. MySQL software today powers a variety of applications, including Internet websites, e-commerce applications, search engines, data warehouses, embedded applications, high-volume content portals, and mission-critical software systems.



Web Applications
It should come as no surprise that MySQL’s primary applications today lie in the arena of the Web. As websites and web-based distributed applications grow ever more complex, it becomes more and more important that data be managed efficiently to improve transactional efficiency, reduce response time, and enhance the overall user experience. Consequently, a pressing need exists for a data management solution that is fast, stable, and secure—one that can be deployed and used with minimal fuss and that provides solid underpinnings for future development.

MySQL fits the bill for a number of reasons. Its proven track record generates confidence in its reliability and longevity; its open-source roots ensure rapid bug fixes and a continued cycle of enhancements (not to mention a lower overall cost); its portability and support for various programming languages and technologies make it suitable for a wide variety of applications; and its low cost/high performance value proposition makes it attractive to everyone from home users to small- and mediumsized businesses and government organizations. For these reasons and more, MySQL is a key component of modern web applications, particularly those built on the popular LAMP stack.

Wondering what a LAMP stack is? Well, the term refers to a set of open-source software components that are commonly used in conjunction with each other to build web-based applications. These components are
• A base operating system and server environment (Linux)
• A web server (Apache) to intercept HTTP requests and either serve them directly or pass them on to the PHP interpreter for execution
• A database engine (MySQL) that holds application data, accepts connections from the application layer, and modifies or retrieves data from the database
• A programming toolkit (PHP, Perl, or Python) that parses and executes program code, processes database results, and returns results to the client



Data Warehouses
As the opening paragraph in this chapter notes, businesses are becoming more and more intelligent in how they store, filter, and use information. Data warehouses are a key source of this business intelligence. Typically, data in a data warehouse is gathered from an enterprise’s internal information systems, linked, and stored for long periods of time. In its simplest form, this data merely provides a record of past events; however, it can also be “mined” to detect patterns, which serve as input into an organization’s decision-making. Speed of data retrieval is thus one crucial component of a data warehouse; long-term reliability is another.

MySQL scores high on both counts. It supports engine-level data integrity through the use of primary key and foreign key constraints. An extremely efficient querycaching mechanism dramatically enhances response times for queries that are called upon to retrieve the exact same data as a previous query. MySQL InnoDB table format uses asynchronous I/O and a sequential read-ahead buffer to improve data retrieval speed, and a “buddy algorithm” and Oracle-type tablespaces for optimized file and memory management. For data storage reliability, MySQL supports replication, a data distribution mechanism that places copies of tables and databases in remote locations to reduce downtime in case of a server failure.



Business Applications
As a reliable, feature-rich database server, MySQL also has applications in business, education, science, and engineering—a fact amply demonstrated by its customer list, which includes such names as Motorola, Sony, NASA, HP, Xerox, and Silicon Graphics. Whether it is small, embedded applications or high-availability data processing systems, MySQL offers the scalability and performance needed to achieve business objectives. The MySQL website states that “MySQL scales to deal with billions of rows and terabytes of data, making it suitable for a wide range of transactional and analytic applications.”

To take advantage of multiprocessor architecture, MySQL is built using a multithreaded design, which allows threads to be allocated between processors to achieve a higher degree of parallelism. MySQL’s clustering technology allows data to be distributed across multiple nodes to achieve greater redundancy, while its fully ACID-compliant transactional engine provides a high degree of safety from undetected data loss. At the other end of the scale, MySQL’s embedded server library has a 1-MB memory/4-MB disk space footprint and provides a multithreaded, cross-platform data storage engine for use in kiosk-style applications or appliances. Finally, MySQL uses a two-tier privilege system (at the connection level and at the individual object level) to ensure the security and integrity of its data, and supports the SSL encryption protocol for client/server communication.


Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

MySQL is based on a tiered architecture, consisting of both primary subsystems and support components that interact with each other to read, parse, and execute queries, and to cache and return query results.



Subsystems
There are three primary subsystems within the MySQL architecture, as discussed in the following sections.

Memory and Connection Management
This subsystem manages user connections, via modules for network connection management with clients, and synchronizes competing tasks and processes, via modules for multithreading, thread locking, and performing thread-safe operations. It also handles all memory management issues between requests for data by the query subsystem and the data storage subsystem.

Query Parsing and Execution
Query parsing and execution is handled by two interrelated components: the syntax parser and the query optimizer. The syntax parser decomposes the SQL commands it receives from calling programs into a form that can be understood by the MySQL engine. It also checks the objects being referenced to ensure that the privilege level of the calling program allows it to use them. The query optimizer then prepares the most efficient plan for query execution, making decisions about table-versus-index scans, join methods, and range optimization, and using a bottom-up methodology to detect the optimal execution plan.

Data Storage
The data storage subsystem interfaces with the operating system (OS) to write to disk all of the data in the user tables, indexes, and logs, as well as the internal system data. MySQL 5.1 also introduced a new pluggable architecture, which allows developers to create new table storage mechanisms and “plug them in” to the server at run-time. This pluggable architecture also creates a level of abstraction between the data storage subsystem and the rest of the MySQL server, making it possible for developers to add new data storage engines that interact with the other MySQL subsystems through a standard API.



Connectivity
MySQL is designed on the assumption that the vast majority of its applications will be running on a TCP/IP (Transmission Control Protocol/Internet Protocol) network. This is a fairly good assumption, given that TCP/IP is not only highly robust and secure, but is also common to UNIX, Windows, OS/2, and almost any other serious operating system you’ll likely encounter. When the client and the server are on the same UNIX machine, MySQL uses TCP/IP with UNIX sockets, which operate in the UNIX domain; that is, they are generally used between processes on the same UNIX system, as opposed to Internet sockets, which operate between networks.



Standards Compliance
The Structured Query Language (SQL) is an open standard that has been maintained by the American National Standards Institute (ANSI) since 1986. Although it’s true that the implementation of this standard does differ in varying degrees from vendor to vendor, it’s fair to say that SQL is today one of the most widely used cross-vendor languages. As with other implementations, such as SQL Server’s T-SQL (Transact-SQL) and Oracle’s SQL, MySQL has its own variations of the SQL standard that add power beyond what is available within the standard. Beginning with v5.1, MySQL also includes support for data import and export using Extensible Markup Language (XML), a widely accepted, vendor-neutral format for data markup and sharing.



Transactions
In the SQL context, a transaction consists of one or more SQL statements that operate as a single unit. Each SQL statement in such a unit is dependent on the others, and the unit as a whole is indivisible. If one statement in the unit does not complete successfully, the entire unit will be rolled back, and all the affected data will be returned to the state it was in before the transaction was started. Thus, a transaction is said to be successful only if all the individual statements within it are executed successfully. The MySQL transaction system fully satisfies the ACID tests for transaction safety via its InnoDB and BDB table types (older table types, such as the MyISAM type, do not support transactions).

• Atomicity is handled by storing the results of transactional statements (the modified rows) in a memory buffer and writing these results to disk and to the binary log from the buffer only once the transaction is committed. This ensures that the statements in a transaction operate as an indivisible unit and their effects are seen either collectively or not at all.

• Consistency is primarily handled by MySQL’s logging mechanisms, which record all changes to the database and provide an audit trail for transaction recovery. In addition to the logging process, MySQL provides locking mechanisms that ensure that all of the tables, rows, and indexes that make up the transaction are locked by the initiating process long enough to either commit the transaction or roll it back.

• Server-side semaphore variables and locking mechanisms act as traffic managers to help programs manage their own isolation mechanisms. MySQL’s BDB table handler, for example, uses page-level locking to safely handle multiple simultaneous transactions, while the InnoDB table handler uses a more fine-grained row-level locking.

• MySQL implements durability by maintaining a binary transaction log file that tracks changes to the system during the course of a transaction. In the event of a hardware failure or abrupt system shutdown, recovering lost data is a relatively straightforward task by using the last backup in combination with the log when the system restarts.

Because transactional tables incur some performance overhead, it’s also possible to specify whether to use transactions on a per-table basis.



Query Caching
If a query returns a given set of records, repeating the same query should return the same set of records, unless the underlying data has somehow changed. As obvious as this sounds, few of the other major RDBMS vendors provide features that take advantage of this principle. Other database products are efficient in storing optimized access plans that detail the process by which data is retrieved; such plans allow queries similar to those that have been issued previously to bypass the process of analyzing indexes yet again to get to the data.

Result-set caching takes this principle a step further by storing the result sets themselves in memory, thus circumventing the need to search the database at all. The data from a query is simply placed in a cache, and when a similar query is issued, this data is returned as if in response to the query that created it in the first place. The MySQL engine uses an extremely efficient result set–caching mechanism, known as the Query Cache, that dramatically enhances response times for queries that are called upon to retrieve the exact same data as a previous query. This mechanism is so efficient that a major computing publication declared MySQL queries to be faster than those of Oracle and SQL Server (which are both known for their speed). If implemented properly, decision support systems using MySQL with canned reports or data-driven web pages can provide response speeds far beyond those that would be expected without the Query Cache.



Extensibility
In keeping with its open-source roots, MySQL makes the original source code available as part of the distribution, which permits developers to add new functions and features that are compiled into the engine as part of the core product. MySQL also allows separate C and C++ libraries to be loaded in the same memory space as the engine when MySQL starts up.

MySQL also allows developers to add new functions at run-time through a special user-defined function interface. User-defined functions are created initially as special C/C++ libraries and are then added and removed dynamically by means of the CREATE FUNCTION and DROP FUNCTION statements.



Symmetric Multiprocessing Support
To take advantage of multiprocessor architecture, MySQL is built using a multithreaded design, which allows threads to be allocated between processors to achieve a higher degree of parallelism. This is important to know not only for the database administrator, who needs to understand how MySQL takes best advantage of processing power, but also for developers, who can extend MySQL with custom functions. All custom functions must be thread-safe—that is, that they must not interfere with the workings of other threads in the same process as MySQL.

MySQL makes use of various thread packages, depending on the platform. POSIX threads are used on most UNIX variants, such as FreeBSD and Solaris. LinuxThreads are used for Linux distributions. For efficiency reasons, Windows threads are used on the Windows platform, but the code that handles them is designed to simulate POSIX threads. Because MySQL is a threaded application, it is able to let the operating system take over the task of coordinating the allocation of threads to balance the workload across multiple processors. MySQL uses a global connection thread to handle all connection requests and creates a new dedicated thread to handle authentication and SQL query processing for each connection. In addition, in replication, master-host synchronization is handled by separate threads.

Of course, another way to take advantage of multiprocessing is to run multiple instances of MySQL on the same machine, thereby spawning a separate process for each instance. This approach is especially practical for hosting companies and even for internal hosting within corporate environments. By running multiple instances of MySQL on the same computer, you can easily accommodate multiple user bases that need different configuration options.



Security
The process of accessing a MySQL database can be broken down into two tasks: connecting to the MySQL server itself and accessing individual objects, such as tables or columns, in a database. MySQL has built-in security to verify user credentials at both stages.

• MySQL manages user authentication through user tables, which check not only that a user has logged on correctly with the proper username and password, but also that the connection is originating from an authorized TCP/IP address.

• Once a user is connected, a system administrator can bestow user-specific privileges on objects and on the actions that can be taken in MySQL. For example, you might allow fred@thiscompany.com to perform only SELECT queries against an inventory table, while allowing anna@thatcompany.net to run INSERT, UPDATE, and DELETE statements against the same table.

The actual data that travels over a network, such as query results, isn’t encrypted and is, therefore, open to viewing by a hacker. To secure your data, you can use one of the Secure Shell (SSH) protocols; you’ll need to install it on both the client applications and the operating system you’re using. If you’re using MySQL 4.0 or later, you can also use the Secure Socket Layer (SSL) encryption protocol, which can be configured to work from within MySQL, making it safe for use over the Internet or other public network infrastructures.



Application Programming Interfaces
For application developers, MySQL provides a client library that is written in the C programming language and a set of APIs that provide an understandable set of rules by which host languages can connect to MySQL and send commands. Using an API protects client programs from any underlying changes in MySQL that could affect connectivity. Currently, MySQL provides hooks to C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl, and connectors are also available for JDBC, ODBC, and .NET applications.


Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

In addition to the core MySQL database server, Oracle makes available a number of MySQL-related products and tools. This section introduces you to some of the other members of the MySQL product family.

MySQL Server
This core product consists of a high-performance database server, which is the main software engine responsible for creating and managing databases, executing queries and returning query results, and maintaining security. This core product also includes a number of client-side tools, such as a command-line SQL client; tools to manage user permissions; and utilities to import, export, copy, and repair databases.

MySQL Cluster
MySQL Cluster is a version of the MySQL database server that supports “clustering,” a technology that allows data to be transparently distributed across two or more physical servers to increase redundancy. This clustering technology plays an important role in high-availability applications, as it ensures continuous data availability even if one of the nodes in the cluster fails. At the time of this writing, MySQL Cluster supports up to 255 nodes in a single cluster and uses synchronous replication to copy data between nodes.

MySQL Proxy
MySQL Proxy is a proxy server that serves as a “gatekeeper” between the MySQL database server and connecting clients. It includes the ability to intercept and rewrite queries, modify result sets, implement query queues, analyze query traffic for reporting purposes, and perform load balancing tasks.

MySQL Administrator
MySQL Administrator is an all-in-one control center for a MySQL database server, allowing database administrators to track server status in real time. It includes visual tools for user administration, database backup and restore, and log analysis, as well as server fine-tuning.

MySQL Query Browser
MySQL Query Browser is a visual tool for graphically constructing queries and viewing the results. It includes tools to manage database connections, databases, and tables, as well as a debugger (with breakpoint support) to assist in optimizing and troubleshooting complex queries.

MySQL Workbench
MySQL Workbench is a visual design tool that enables database administrators and developers to graphically design and validate data models, generate database schema code, and manage changes to database schemas. It also includes the ability to visually compare and synchronize two versions of a database and create import/export scripts to transfer data from one system to another.

MySQL Migration Toolkit
The MySQL Migration Toolkit is a graphical, wizard-driven tool to port databases from other RDBMS products to MySQL. It includes support for Oracle, Microsoft SQL
Server, and Microsoft Access, and provides automated tools to remap and rebuild table schemas; copy records; and transfer indexes, views, triggers, and stored procedures.

MySQL Embedded Server
MySQL Embedded Server is a low-footprint version of the MySQL database server that is intended specifically for use in embedded applications, such as networking equipment, diagnostic tools, or point-of-sale (POS) systems. This embedded database also includes a number of useful administrative features: automatic space expansion, auto-restart, and dynamic reconfiguration.

MySQL Drivers and Connectors
MySQL provides drivers and connectors for many different programming languages, thereby making it possible to build database-driven applications using any one of several different development toolkits. Currently, MySQL provides drivers and connectors for C, C++, Java, Perl, PHP, Python, Ruby, JDBC, ODBC, and .NET applications.


Are there Different Versions of the MySQL Database Server?
MySQL’s core database server comes in two flavors: Community and Enterprise. The Community server is “free”: Users can download and use it at no cost under the terms of the GNU GPL, but by the same token, are required to perform all maintenance and administrative tasks themselves, with no support from the MySQL development team. For companies and individuals looking for a greater level of support, the Enterprise server is a commercial offering that provides regular updates and bug fixes, consultancy services and advice from MySQL engineers, and proprietary database-monitoring software in return for a subscription fee.


Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

Part of the reason for MySQL’s blazing performance is its fully multithreaded architecture, which allows multiple concurrent accesses to the database. This multithreaded architecture is the core of the MySQL engine, allowing multiple clients to read the same database simultaneously and providing a substantial performance gain. The MySQL code tree is also structured in a modular, multilayered manner, with minimum redundancies and special optimizers for such complex tasks as joins and indexing.

MySQL also includes a query cache, which can substantially improve performance by caching the results of common queries and returning this cached data to the caller without having to re-execute the query each time. This is different from competing systems such as Oracle, in that those systems merely cache the execution plan, not the results. However, they still need to execute the query, including all joins, and re-retrieve the query results on every run. MySQL benchmarks claim that this feature improves performance by more than 200 percent, with no special programming required on the part of the user2. It is worth noting that MySQL’s designers initially left out many of the features that cause performance degradation on competing systems, including transactions, referential integrity, and stored procedures. These features typically add complexity to the server and result in a performance hit. User requests for these features have, however, resulted in their inclusion in newer versions of the product.


Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

MySQL’s popularity is due to a particular combination of unique features: speed, reliability, extensibility, and open-source code. The following sections discuss these features in greater detail.

Speed
In an RDBMS, speed—the time it takes to execute a query and return the results to the caller—is everything. By any standards, MySQL is fast, often orders of magnitude faster than its competition. Benchmarks available on the MySQL website show that MySQL outperforms almost every other database currently available, including commercial counterparts like Microsoft SQL Server 2000 and IBM DB2. For example, an eWeek study in February 2002 that compared IBM DB2, Microsoft SQL Server, MySQL, Oracle9i, and Sybase concluded that “MySQL has the best overall performance and that MySQL scalability matches Oracle … MySQL had the highest throughput, even exceeding the numbers generated by Oracle.”1

Reliability
Most of the time, high database performance comes at a price: low reliability. MySQL is, however, designed to offer maximum reliability and uptime, and it has been tested and certified for use in high-volume, mission-critical applications. MySQL supports transactions, which ensure data consistency and reduce the risk of data loss, and replication and clustering, two techniques that significantly reduce downtime in the event of a server failure. Finally, MySQL’s large user base assists in rapidly locating and resolving bugs and in testing the software in a variety of environments; this proactive approach has resulted in software that is virtually bug-free.

Scalability
MySQL can handle extremely large and complex databases without too much of a performance drop. Tables of several gigabytes containing hundreds of thousands of records are not uncommon, and the MySQL website itself claims to use databases containing 50 million records. A 2005 test by MySQL Test Labs demonstrated that “MySQL shows near-linear scalability in a multi-CPU environment,”3 with performance increasing in proportion to the number of CPUs added to the system. This ability to scale with demand has made MySQL popular with businesses like Eli Lilly, Alstom, Dun & Bradstreet, Epson, and the New York Times; high-volume websites such as Google, Facebook, and Slashdot; and government organizations such as NASA, the U.S. Census Bureau, and the Swedish National Police.

Ease of Use
MySQL is so easy to use that even a novice can pick up the basics in a few hours, and the software is well supported by a detailed manual, a large number of free online tutorials, a knowledgeable developer community, and a fair number of books. While most interaction with MySQL takes place through a command-line interface, a number of graphical tools, both browser-based and otherwise, are also available to simplify the task of managing and administering the MySQL database server. Finally, unlike its proprietary counterparts, which have literally hundreds of adjustable parameters, MySQL is fairly easy to tune and optimize for even the most demanding applications. For commercial environments, MySQL is fully supported in terms of professional MySQL training, consultancy, and technical support.

Portability and Standards Compliance
MySQL supports most of the important features of the ANSI (American National Standards Institute) SQL standard, and often extends the ANSI standard with custom extensions, functions, and data types designed to improve portability and provide users with enhanced functionality. MySQL is also available for both UNIX and non-UNIX operating systems, including Linux; Solaris; FreeBSD; OS/2; MacOS; and Windows 95, 98, Me, 2000, XP, NT, and Vista; and it runs on a range of architectures, including Intel x86, Alpha, SPARC, PowerPC, and IA64.

Multiuser Support
MySQL is a full multiuser system, which means that multiple clients can access and use one (or more) MySQL database(s) simultaneously; this is of particular significance during development of web-based applications, which are required to support simultaneous connections by multiple remote clients. MySQL also includes a powerful and flexible privilege system that allows administrators to protect access to sensitive data using a combination of user- and host-based authentication schemes.

Internationalization
As a program that is used by millions of users in countries across the globe, it would be unusual indeed if MySQL did not include support for various languages and character sets. MySQL offers full Unicode support, as well as full support for most important character sets (including Latin, Chinese, and European character sets). Character sets are taken into account when sorting, comparing, and saving data.

Wide Application Support
MySQL exposes application programming interfaces (APIs) to many programming languages, thereby making it possible to write database-driven applications in the language of your choice. Currently, MySQL provides hooks to C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl, and connectors are available for JDBC, ODBC, and .NET applications.

Open-Source Code
The MySQL source code is freely available under the terms of the GNU General Public License—a key benefit, since it allows users to download and modify the application to meet their specific needs. This unique licensing policy has fuelled MySQL’s popularity, creating an active and enthusiastic global community of MySQL developers and users. This community plays an active role in keeping MySQL ahead of its competition, both by crash-testing the software for reliability on millions of installations worldwide and extending the engine to stay abreast of the latest technologies. High-volume, well informed mailing lists and user groups assist in the rapid resolution of questions and problems, and a global network of committed MySQL users and developers provides knowledgeable advice, bug fixes, and third-party utilities. All of this has paid off: A code inspection study by Reasoning, Inc. concluded that the code quality of MySQL was six times better than that of comparable proprietary code.


It is worth noting that if your MySQL-powered application is not licensed under the GPL or other MySQL-approved open-source license and you intend to redistribute it (whether internally or externally), you are required to purchase a commercial license for this use. Oracle earns revenue both from the sale of these licenses and by providing support, training, and consultation services for the MySQL database server.


Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

MySQL came into being in 1979, when Michael “Monty” Widenius created a database system named UNIREG for the Swedish company TcX. UNIREG didn’t, however, have a Structured Query Language (SQL) interface—something that caused it to fall out of favor with TcX in the mid-1990s. So TcX began looking for alternatives. One of those alternatives was mSQL, a competing DBMS created by David Hughes. mSQL didn’t work for TcX either, however, so Widenius decided to create a new database server customized to his specific requirements. That system, completed and released to a small group in May 1996, became the first version of what is today known as MySQL.

A few months later, MySQL 3.11 saw its first public release as a binary distribution for Solaris. Linux source and binaries followed shortly; an enthusiastic developer community and a friendly, General Public License (GPL)-based licensing policy took care of the rest. Today, MySQL is available for a wide variety of platforms, including Linux, MacOS, and Windows, in both source and binary form. A few years later, TcX spun off MySQL AB, a private company that had sole ownership of the MySQL server source code and trademark, and was responsible for maintenance, marketing, and further development of the MySQL database server. It was managed by Michael Widenius, David Axmark, and Allan Larsson, supported by both a full-time staff and the active support of a worldwide developer community.

In 2008, MySQL AB was formally acquired by Sun Microsystems, and in 2009, Sun Microsystems was in turn acquired by Oracle, which today owns and develops the MySQL database engine. Although Oracle operates commercially in a number of different markets, the MySQL source code remains available to the community under the GNU General Public License (users can, however, purchase commercial support from MySQL).

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition

• 0 comments »

This section summarizes general principles of preventive maintenance. Later sections provide details on implementing these principles.
To prepare in advance against database problems, take the following actions:

• Enable the auto-recovery capabilities that the MySQL server provides.

• Set up scheduled preventive maintenance to perform table checking periodically.
Routine table-checking procedures can help you detect and correct minor problems before they become worse.

• Set up a database backup schedule. Should the worst occur and you be faced with catastrophic system failure, you’ll need the backups to perform recovery operations. Enable your binary log, too, so that you have a record of updates that took place after the backup was made. Binary logging provides significant advantages for backup and replication and has negligible performance overhead (about 1%), so there is little reason not to enable it.


If table damage or data loss does occur despite your efforts, exercise your options for dealing with such problems:

• Check your tables, and then fix any that are found to be corrupt if possible. Minor damage often can be corrected by using MySQL’s table repair capabilities.

• For circumstances under which table checking and repair isn’t sufficient to get you up and running, perform data recovery using your backups and your binary log. Begin by using the backups to restore your tables to their state at the time of the backup. After that, use the log files to re-apply any updates that were made after the backup, to bring your tables to their state when the crash occurred.


The tools at your disposal for carrying out these tasks include the capabilities of the
MySQL server itself and also several other utilities included in the MySQL distribution:

• When the server starts, transactional storage engines can perform auto-recovery.You can also enable automatic table repair for the MyISAM storage engine.These capabilities are useful when the server restart follows a crash.

• Use the mysqldump or mysqlhotcopy program to make backups of your databases, should you need to recover them later.

• To tell the server to perform table maintenance operations on demand, use SQL statements such as CHECK TABLE and REPAIR TABLE. For a command-line interface to these statements, use the mysqlcheck program.The myisamchk utility also can check tables for problems and perform various corrective actions.


Some of these programs, such as mysqlcheck and mysqldump,work in cooperation with the server.They connect as clients to the server and issue SQL statements that instruct the server what kind of table maintenance operation to perform. By contrast, myisamchk is an independent standalone program that operates directly on the files used to represent tables. Because the server also accesses those files while it runs, myisamchk acts in effect as a competitor to the server.This means that you must take steps to prevent
myisamchk and the server from interfering with each other. For example, if you’re repairing a table with myisamchk, it’s necessary to keep the server from trying to write to the table at the same time. Failure to do so can result in much worse problems than those you’re trying to correct!


Source of Information : MySQL (4th Edition)

• 0 comments »