indexes trade space for performance. But sometimes you’d rather not trade too much space for the performance you’re after. Luckily, MySQL gives you a lot of control over how much space is used by the indexes. Maybe you have a phone_book table with 2 billion rows in it. Adding an index on last_name will require a lot of space. If the average last_name is 8 bytes long, you’re looking at roughly 16 GB of space for the data portion of the index; the row pointers are there no matter what you do, and they add another 4-8 bytes per record.[2]
[2] That’s a bit of an oversimplification, too. MySQL has some strategies for reducing the size of the index, but they also come at a price.
Instead of indexing the entire last name, you might index only the first 4 bytes:
ALTER TABLE phone_book ADD INDEX (last_name(4))
In doing so, you’ve reduced the space requirements for the data portion of the index by roughly half. The trade-off is that MySQL can’t eliminate quite as many rows using this index. A query such as:
SELECT * FROM phone_book WHERE last_name = ‘Smith’
retrieves all fields beginning with Smit, including all people with name Smith, Smitty, and so on. The query must then discard Smitty and all other irrelevant rows.
