MySQL types and primary key

We haven't talked about the primary key directive in the create table command.

create table users
(
    ...
    primary key    (user_id),
    ...
)

This is an instruction to the MySQL "secretary" to file things by user_id. The constraint imposed on a primary key is that each row must have a unique value for the key. Technically, MySQL creates a B-tree to make lookup of a specific row by user_id fast. So this

select * from users where user_id = 2;

is faster than

select * from users where favorite_number = 945;

Is MySQL limited to small data types? Only if you think 4 gigabytes is small. That's what the LongBlob and LongText types can hold.

Let's say we want to create a message system. A simple example of a message row is created with:

create table messages
(
    id           int auto_increment not null,
    user_id      int not null,
    posting_date datetime not null,
    comment_body text
    primary key  (id)
)

This introduces two new SQL types: datetime and text.

The datetime column data is structured like so, "YYYY-MM-DD hh:mm:ss". This way, datetimes can be ASCII sorted into chronological order. To us, it's a string, that's the format we give to MySQL and the format we get out, but internally, it's an 8 byte bitstream.

The text type holds up to 64Kb of data, more than enough for a message.

The user_id column, that's the relational part of Relational Database Management System (RDBMS). The user_id in our example references the id column of the users table. This way we're relating the message to the user who is the author. The table is the structural foundation but this concept of referencing is the functional basis for RDBMS.

Here's an example message row (the message_body can be much longer):

+----+---------+---------------------+--------------+
| id | user_id |        posting_date | message_body |
+----+---------+---------------------+--------------+
|  1 |       3 | 2000-10-10 10:00:00 |      Wassup! |
+----+---------+---------------------+--------------+

Let's say we have a voting system, where users can vote on whether or not the message was worth reading. We'd create a table like this:

create table message_votes
(
    message_id  int not null,
    user_id     int not null,
    vote        enum('good', 'bad') not null,
    primary key (message_id, user_id)
);

In this example, the vote column can contain either the value 'good' or the value 'bad'.

The primary key directive specifies two columns to "file" by. Since primary keys are by definition unique, (message_id, user_id) as a value pair must be unique. This imposes the constraint that each user can only vote on a specific message once.

Also, in this example, MySQL "files" message votes "sorting" by message_id first then by user_id. Which means lookups like:

select * from message_votes where message_id = 3;

are going to be faster than

select * from message_votes where user_id = 2;

But the fastest way to lookup a message vote is:

select * from message_votes where 
    message_id = 3 and user_id = 2;

Now we're ready to use Perl DBI to interface with MySQL.