Databases Reference
In-Depth Information
... omitted ...
) ENGINE=InnoDB;
We then measured the performance of joining the tables by the primary key columns.
Here is the query we used:
mysql>
SELECT SQL_NO_CACHE COUNT(*)
->
FROM webservicecalls
->
JOIN webservicecalls USING(day, account, service, method);
We varied this query to join the
VARCHAR
and
ENUM
columns in different combinations.
Table 4-1
shows the results.
Table 4-1. Speed of joining VARCHAR and ENUM columns
Test
Queries per second
VARCHAR
joined to
VARCHAR
2.6
VARCHAR
joined to
ENUM
1.7
ENUM
joined to
VARCHAR
1.8
ENUM
joined to
ENUM
3.5
The join is faster after converting the columns to
ENUM
, but joining the
ENUM
columns to
VARCHAR
columns is slower. In this case, it looks like a good idea to convert these col-
umns, as long as they don't have to be joined to
VARCHAR
columns. It's a common design
practice to use “lookup tables” with integer primary keys to avoid using character-based
values in joins.
However, there's another benefit to converting the columns: according to the
Data_
length
column from
SHOW TABLE STATUS
, converting these two columns to
ENUM
made
the table about 1/3 smaller. In some cases, this might be beneficial even if the
ENUM
columns have to be joined to
VARCHAR
columns. Also, the primary key itself is only about
half the size after the conversion. Because this is an InnoDB table, if there are any other
indexes on this table, reducing the primary key size will make them much smaller, too.
We explain this in the next chapter.
Date and Time Types
MySQL has many types for various kinds of date and time values, such as
YEAR
and
DATE
. The finest granularity of time MySQL can store is one second. (MariaDB has
microsecond-granularity temporal types.) However, it can do temporal
computations
with microsecond granularity, and we'll show you how to work around the storage
limitations.
Most of the temporal types have no alternatives, so there is no question of which one
is the best choice. The only question is what to do when you need to store both the
date and the time. MySQL offers two very similar data types for this purpose:
DATE
