Search code examples
mysqlsqlperformanceindexing

"key_len" column is more important or "rows" column in the result of EXPLAIN?

I have executed a query with two different kind of indexes. Here is the result of them:

First kind of index:

enter image description here

Second kind of index:

enter image description here

As you see, the first one has a bigger number in the "key_len" column. And the second one has a bigger number in the "rows" column.

I want to know, which one is more efficient ?

Note: My dataset is pretty small.

Solution

  • Short answer: Rows is more important than key_len.

    With those tiny numbers, you can't predict much. Here are some tips:

    INT is 4 bytes, BIGINT is 8 bytes. NULL adds 1. So I guess key_len=9 is a NULLable BIGINT? (It would really help if you provided SHOW CREATE TABLE and SELECT....) key_len=5 might be a nullable INT. Use NOT NULL wherever appropriate.

    VARCHAR(255) CHARSET utf8mb3 will weigh in as key_length=767.

    VARCHAR(255) CHARSET utf8mb4 will weigh in as key_length=1022.

    (utf8 is a synonym for one of the above.)

    Rows (3 or 4) on the <derived> table is an exact count, since EXPLAIN evaluated it. Rows on other lines are approximate; sometimes they are off (either direction) by a factor of 2, maybe more.

    eq_ref Rows=1 is common for essentially looking up by a UNIQUE or PRIMARY key. So that is likely to be exact, except when the row is missing.

    A crude (very crude) way to judge an EXPLAIN is to multiply the numbers in the Rows column. In your cases, you get 9 and 32. But I would not jump to the conclusion that the 9 is faster than 32. If it were 9000000 versus 32000, then I might jump to a conclusion.

    The most common mistake for a novice is not understanding that INDEX(last_name, first_name) is often more performant than INDEX(last_name) , INDEX(first_name).