Using index with large key_len I was trying to optimize my query as I saw it pop up on my slow query log. I am not completely sure I understand what I am saying, but when I ran explain on my MYSQL query it showed me something like this.

+------+-------------+--------------------------+--------+---------------+---------+---------+-------------------------------------------+------+-------------+
| id   | select_type | table                    | type   | possible_keys | key     | key_len | ref                                       | rows | Extra       |
+------+-------------+--------------------------+--------+---------------+---------+---------+-------------------------------------------+------+-------------+
|    1 | SIMPLE      | subject                  | ALL    | NULL          | NULL    | NULL    | NULL                                      | 1144 | Using where |
|    1 | SIMPLE      | asset                    | eq_ref | PRIMARY       | PRIMARY | 4       | cool2                                     | 1    | Using where |
|    1 | SIMPLE      | asset_type               | eq_ref | PRIMARY       | PRIMARY | 4       | cool                                      | 1    | Using where |
+------+-------------+--------------------------+--------+---------------+---------+---------+-------------------------------------------+------+-------------+



When I added an index on one of the fields, and ran explain on the query, I got:

+------+-------------+--------------------------+--------+-------------------------------------+-------------------------------------+---------+-------------------------------------------+------+-------------+
| id   | select_type | table                    | type   | possible_keys                       | key                                 | key_len | ref                                       | rows | Extra       |
+------+-------------+--------------------------+--------+-------------------------------------+-------------------------------------+---------+-------------------------------------------+------+-------------+
|    1 | SIMPLE      | subject                  | ref    | new_index                           | new_index                           | 503     | const                                     | 1    | Using where |
|    1 | SIMPLE      | asset                    | eq_ref | PRIMARY                             | PRIMARY                             | 4       | cool2                                     | 1    | Using where |
|    1 | SIMPLE      | asset_type               | eq_ref | PRIMARY                             | PRIMARY                             | 4       | cool                                      | 1    | Using where |
+------+-------------+--------------------------+--------+-------------------------------------+-------------------------------------+---------+-------------------------------------------+------+-------------+


Can anyone please explain to me what is the trade-off between rows and key_len. Does a big key_len make a difference? Do I have a big key_len?
Look at the differences between the two EXPLAINs...

ALL  NULL       NULL   1144
REF  new_index  const     1


The first looked at ALL 1144 (approximate) rows of the table

The second used new_index, checking against a "constant", finding 1 (approximate) row.  Clearly faster.

The 503 may be 2+3*167, Perhaps the column is VARCHAR(167) CHARACTER SET utf8 NOT NULL?  The "2" is overhead for "VAR"; the "3" is the max byte length of a utf8 character.  About the only use for "503" is exactly what I just did -- deduce something about the index in question.

Or maybe it is a VARCHAR(125) CHARACTER SET utf8mb4 NULL?  2+4*125+1; the "1" is for a NULL flag.

The size of the "key_len" does not matter, except that there are limits -- 767, 3072, and possibly others, depending on version, etc.

Index "prefixing" (as danblack mentioned) is problematic.  It is often not used when you think it should.  I recommend avoiding it in most cases.  This is a case where you might need it, assuming you can't shrink the TEXT column:

CREATE TABLE ...
    foo TEXT NOT NULL, ...
    INDEX(foo(167)) ...