PHP —

Limitations of MySQL

The most significant limitation of MySQL is that it doesn't support nested queries. However, support is planned in MySQL Version 4. Nested queries are those that contain another query. Consider an example nested query to find the wines that have inventory stock:

SELECT DISTINCT wine_id FROM wine
WHERE wine_id IN
  (SELECT wine_id from inventory);

The query returns the wine_id values from the wine table that are found in the inventory table. Nested queries use the IN, NOT IN, EXISTS, and NOT EXISTS operators.

In many cases, a nested query can be rewritten as a join query. For example, to find the wines that are in stock, you can use the following join query:

SELECT DISTINCT wine.wine_id FROM wine, inventory
WHERE wine.wine_id = inventory.wine_id;

However, some nested queries can't be rewritten as join queries; for difficult queries, temporary tables are often a useful workaround.

A limitation of DELETE and UPDATE is that only one table can be specified in the FROM clause. This problem is particular to MySQL and related to the lack of support for nested queries. This limitation can make modifications of data difficult. For example, it prevents data being deleted or updated using the properties of another table. A solution involves data being copied to a temporary table using a combined INSERT and SELECT statement that joins together data from more than one table. Then, the data can be deleted or updated in the temporary table and then transferred back to the original table. Another approach, using the concat( ) string function, is discussed in Section 1.4.4.1 in the MySQL manual.

To avoid UPDATE and DELETE problems, consider adding additional attributes to tables at design time. For example, in the winestore we added a DATE attribute to the items table so that shopping-cart items can be removed easily if they aren't purchased within one day. Removing rows from the items table based on the DATE in the orders table is difficult without support for nested queries.

MySQL doesn't support stored procedures or triggers. Stored procedures are queries that are compiled and stored in the DBMS. They are then invoked by the middle-tier application logic, with the benefit that the query is parsed only once and there is less communication overhead between the middle and database tiers. Triggers are similar to stored procedures but are invoked by the DBMS when a condition is met. Stored-procedure support is planned for MySQL, but trigger support isn't.

Views aren't supported in MySQL. Views consolidate read-only access to several tables based on a join condition. For example, a view might allow a user to browse the sales made up to April without the need to create a temporary table, as we did in the example in Section 3.8. View support is planned for the future.

Limitations that we don't discuss here include the lack of support for foreign keys and cursors. More detail on the limitations of MySQL can be found in Section 1.4 of the manual distributed with MySQL.