I dont know what you mean by index size but:

mediumint(9) = 9 is the display width of the value. It has nothing to do with size. A mediumint is always 3 bytes.
varchar(12) = 12 is max string length it can store

An index has also an order so an index with order field1,field2 will be used for queries with field1 or "field1 and field2" but never for field2 alone. Thats why you need the second index.

By "index size", I'm referring to a field in phpMyAdmin.

In PMA, click "Structure", then "+Indexes". In the list of indexes, click "Edit", and the form shows:

Index Name:
Comment:
Index type:

Column  Size

For testing, I plugged in USE INDEX(which) and compared it to USE INDEX(which_6). Using "which" the column "rows" returned 137,169, and using "which_6" returned 122,459.

I think the "size" that you are referring to is the number of characters from the column that will be used to build the index. For example, if you create an index on the 'which' column with a size of six, only the first six characters of each value in that column are indexed.

As to speed, it's a trade-off. Indexing less characters will make the index file smaller and quicker to search. But, it may make the query take longer because more more rows may match the index. And, it can be really bad if, for example, all or many of the values begin with the same characters. Making the number of characters larger will increase the size of the index, which will generally make select queries faster, but may make inserts take a little longer.

In any case, I wouldn't create multiple indexes on one column. That would be a waste of disk space and will slow down inserts and updates.

If you really need to speed up queries (as opposed to just wanting them to be as quick as possible), you may need to look into other ways in addition to indexes. If you are dealing with addresses, you might create a separate table for each state or whatever. Then, knowing the state, you'd only search addresses in that specific state.

Or you might do is to include some sort of "hash" column in your table that is indexed and search by that column. As an example, if you had two values often used for searching, you could insert something like MD5(CONCAT(value1 ,value2) into a column. When querying, you'd search for MD5(CONCAT('actualValue1' ,'actualValue2') in your query. Be careful doing tricky stuff like this because you're basically trying to do what the database software already does with indexes (and it probably does it better).

Sometimes, I'll have multiple tables with the same or similar data which goes against the general notion of normalizing data in databases, but can make some queries much faster because there are tables designed specifically for the queries.

My point is that there are often ways to set up your tables and the values in them to optimize them based on how you use them.

Making the number of characters larger will increase the size of the index, which will generally make select queries faster, but may make inserts take a little longer.

Is that a typo, or am I misunderstanding the logic? I'm trying to make SELECT queries faster, and I thought that a smaller index would do this?

I only made several indexes for the sake of testing which one would be faster. It doesn't make sense, though; on paper the first 3 characters are unique, but using an index size of 6 was generally the fastest. If I used the index size of 3, it was slower, even though that seems like the most logical choice.

FWIW, the column I'm testing has 51 different possible values, and the maximum VARCHAR size is 12.

An index is used to reduce the number of records that the query has to check. A larger index generally results in faster searches because each entry in the index is mapped to fewer possible matches.

For example, assume you were searching a list of common words. Start with a small index that divides the words into two bins (i.e. ones starting with A-M and ones starting with N-Z). When you search for a word like "Test," you would end up looking through all the words that start with N through Z.

Now, if you create a larger index with 26 bins, a bin for each possible first letter, then you would only look at words starting with "T" when searching for "Test."

The second search using the larger index would be faster.