Using prefix primary keys

In this example, we will add indexes to two tables that are almost identical. The only difference will be the definition of their primary keys. You will see the difference in space consumption for secondary indexes between a regular full column primary key and a prefix primary key. The sample table structure and data are designed to demonstrate the effect very evidently. In real-world scenarios the effect will most certainly be less severe.

Getting ready...

Connect to the database server with your administrative account.

How to do it...

1. Download the sample script for this chapter from the book's website and save it to your local disk. In the example below, we will assume it is stored in /tmp/idxsizeestimate_sample.sql.
2. Create a new database and make it the default database:

   CREATE DATABASE pktests;
   USE pktests;
   

3. Import the sample data from the downloaded file. When done, you will be presented with some statistics about the two tables loaded. Note that both tables...