Database Reference
In-Depth Information
19.4 Shortcomings of MySQL
Like all software products, MySQL does have some limitations in its current production
release (version 5.1). What is most impressive about the MySQL venture is that the
developers readily admit the limitations and document them. Following is a summary
of these limitations:
19.4.1 Limitation on Joins and Views
Limitation on Joins:
The maximum number of tables that can be referenced in a single
join is 61. This also applies to the number of tables that can be referenced in the definition
of a view. This is a reasonable threshold that should not cause anyone to lose sleep.
Limitations on Views:
View processing is not optimized in MySQL. Three restrictions
have been documented by MySQL AB:
•
You are not allowed to create indexes on views. This is significant,
so MySQL AB would do well to remove it in the near future.
•
Indexes can be used for views processed using MySQL's
merge
algorithm. However, a view that is processed with the
temptable
algorithm is unable to take advantage of indexes on its underlying
tables (although indexes can be used during generation of the
temporary tables). This is also a significant restriction that should
be lifted in the future.
•
Subqueries cannot be used in the
From-Clause
of a view.
MySQL AB promises to lift this limitation in the future.
19.4.2 Limitations on Sub-queries
MySQL AB lists a number of restrictions on sub-queries, and promises to address them
in the near future. Some of the more prominent ones are mentioned below (for a full list,
go to the MySQL AB site [MySQL AB, 2008]):
If you compare a
null
value to a sub-query using operators
ALL
,
ANY
, or
SOME
, and the subquery returns an empty result,
the comparison might evaluate to the non-standard result of
NULL
rather than to
TRUE
or
FALSE
.
1.
2.
A subquery's outer statement can be any one of the following:
SELECT
,
INSERT
,
UPDATE
,
DELETE
,
SET
, or
DO
.
3.
Sub-query optimization for the IN (<sub-query>) construct is not
as effective as for the equal (
=)
operator or for
IN (<value-list>)
constructs. A typical case for poor performance of the
IN
(<sub-query>) construct is when the sub-query returns a small
number of rows but the outer query returns a large number of
rows to be compared to the sub-query result. The problem is
that, for a statement that uses an
IN
(<sub-query>) construct,
the optimizer rewrites it as a correlated sub-query.
