I'm using PMA to play around with my indexes in an attempt to improve some of the query times.
I've never actually used the "size" field in an index, and really can't find a lot of info on it (probably because "mysql index size" finds results about the data size of the index, not the size field), and the MySQL docs are pretty vague on this. If I understand correctly, though, if I enter a "size" of, say, 6, then the index will store only the first 6 digits of the column.
I have a table with 2 columns:
id -> mediumint(9)
which -> varchar(12)
Both of the columns are in a UNIQUE index (which is used to prevent duplicates in an INSERT query). Column "which" currently has about 50 different possible values, and there are a little over 160,000 rows.
I found that one of my queries ran significantly faster if I added "which" to its own INDEX. Then for testing, I modified that index to a size of 6 (a random number) and found that it ran even faster.
Based on that, I decided to see which "size" would be best. I created 12 indexes on the "which" column; one with a size of 1, one with a size of 2, and so on. Then I used EXPLAIN to show me which index would be chosen by default, and which might give the fastest result.
The query was:
SELECT * FROM tableA WHERE which='whatever';
The first query chose the index with the size of 6 so I thought that, for whatever reason, 6 was the magic number. For that query, "whatever" was 9 characters long.
Then I changed the "WHERE which='whatever'" variable to a value that was 5 characters long, and EXPLAIN chose the index with the size of 3.
Then I changed it to a value that was 6 characters long, it decided that none of the "which" indexes were best and instead used the original UNIQUE index.
There doesn't seem to be any rhyme or reason to that!
Before I start playing with my larger tables and indexes, I'd like to figure out the logic behind how this works. How should I decide the "best" size of an index for the fastest result?