This trick was developed in one of my biggest database. It has 7 million records with 160 fields each. Half as text fields. the size of database after converting to best field size and database had come from 17GB (raw fixed width text) to 5GB MYI with neat RDBMS. Data was 35% while indexes were 65%. It was like this for best search performance. Now the text data like First Name and Last Name were problems. They take ~25 letters each and indexing them was not solving most of the problem.
I knew MySQL like operator was case insentivite. This may sound like good news but it’s not. For every search it anyway has to setcase the field of every record before applying the search function. MySQL is very good in group indexes. Suppose you are searching for vehicle type 17 and widht as 19, a index which uses both will have better performance than index of both independently. Secondly, fixed width indexes are too fast then character. Numeric comparisions are even then varchar comparisions. My problem was to search First Name matches.
The trick uses all the ticks together.
- I made fiurst three letters to upper case.
- I converted first three letters into a 16bit integer (field F).
- Created field L as the balance letters from first name.
- Created index A with field F as the first and field L as the second.
VOLA! Mysql loves me. Explain listed that to scan a particular first name, it did not need a table scan any more. First three letters used to make the scan into very small number of records. Also first three letter were index therefore easily approached from the index.
There must be a question of how was three upper case letters converted to 16 bit integer. Well, it was 15 bits that were used. 
26 possible letters means 5 bit scope. Multriply that with 3 letters and we have 15 bits.
