Is it practical to store string columns in indexes?

Question

Suppose we have this example structure/data:

@see fiddle at http://sqlfiddle.com/#!8/1f85e/1

-- SET GLOBAL innodb_file_per_table=1;
DROP TABLE IF EXISTS mysql_index_reading_myisam;
CREATE TABLE IF NOT EXISTS mysql_index_reading_myisam (
    id INT NOT NULL AUTO_INCREMENT
  , str VARCHAR(50) NOT NULL
  , enm ENUM('thatis', 'thequestion') NOT NULL
  , cnt TINYINT NOT NULL

  , PRIMARY KEY (id)
  , INDEX str_cnt (str, cnt)
  , INDEX enm_cnt (enm, cnt)

) ENGINE=MyISAM CHARSET=Latin1;
INSERT INTO mysql_index_reading_myisam (str, enm, cnt) VALUES
    ('Tobeornottobe', 'Thatis', 1)
  , ('toBeornottobe', 'thatIs', 2)
  , ('tobeOrnottobe', 'ThatIs', 3)
  , ('tobeorNottobe', 'thatis', 4)
  , ('tobeornotTobe', 'THATIS', 5)
;
DROP TABLE IF EXISTS mysql_index_reading_innodb;
CREATE TABLE mysql_index_reading_innodb LIKE mysql_index_reading_myisam;
ALTER TABLE mysql_index_reading_innodb ENGINE InnoDB;
INSERT INTO mysql_index_reading_innodb SELECT * FROM mysql_index_reading_myisam;

EXPLAIN SELECT cnt FROM mysql_index_reading_myisam WHERE str = 'tobeornottobe';
EXPLAIN SELECT cnt FROM mysql_index_reading_innodb WHERE str = 'tobeornottobe';
EXPLAIN SELECT cnt FROM mysql_index_reading_myisam WHERE enm = 'thatis';
EXPLAIN SELECT cnt FROM mysql_index_reading_innodb WHERE enm = 'thatis';

Let's check how it's stores internally

# egrep --ignore-case --only-matching --text '(tobeornottobe|thatis)' *
mysql_index_reading_innodb.frm:thatis
mysql_index_reading_innodb.ibd:Tobeornottobe
mysql_index_reading_innodb.ibd:toBeornottobe
mysql_index_reading_innodb.ibd:tobeOrnottobe
mysql_index_reading_innodb.ibd:tobeorNottobe
mysql_index_reading_innodb.ibd:tobeornotTobe
mysql_index_reading_innodb.ibd:Tobeornottobe
mysql_index_reading_innodb.ibd:toBeornottobe
mysql_index_reading_innodb.ibd:tobeOrnottobe
mysql_index_reading_innodb.ibd:tobeorNottobe
mysql_index_reading_innodb.ibd:tobeornotTobe
mysql_index_reading_myisam.frm:thatis
mysql_index_reading_myisam.MYD:Tobeornottobe
mysql_index_reading_myisam.MYD:toBeornottobe
mysql_index_reading_myisam.MYD:tobeOrnottobe
mysql_index_reading_myisam.MYD:tobeorNottobe
mysql_index_reading_myisam.MYD:tobeornotTobe
mysql_index_reading_myisam.MYI:Tobeornottobe
mysql_index_reading_myisam.MYI:toBeornottobe

• In both engines enums are stored in *.frm as it should be. Ok.
• In both engines data stored in data and data/index files. Ok.
• In MyISAM index has two records.
• In InnoDB index has all five records in correct case.

What I've found already

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

In some cases, a query can be optimized to retrieve values without consulting the data rows. If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed:

SELECT key_part3 FROM tbl_name WHERE key_part1=1

http://www.mysqlperformanceblog.com/2009/09/12/3-ways-mysql-uses-indexes/

Using index to read data Some storage engines (MyISAM and Innodb included) can also use index to read the data, hence avoiding to read the row data itself. This is not simply savings of having 2 reads per index entry instead of one but it can save IO orders of magnitude in some cases – Indexes are sorted (at least on the page boundary) so doing index range scan you typically get many index entries from the same page but the rows itself can be scattered across many pages requiring potentially a lot of IOs. On top of that if you just need access to couple of columns index can be simply much smaller than the data which is one of the reason covering indexes help to speed up queries even if data is in memory. If MySQL is only reading index and not accessing rows you will see “using index” in EXPLAIN output.

Then in sources of sql_select.cc: http://bazaar.launchpad.net/~mysql/mysql-server/5.1/view/head:/sql/sql_select.cc#L12834

/*
  We can remove binary fields and numerical fields except float,
  as float comparison isn't 100 % secure
  We have to keep normal strings to be able to check for end spaces
*/
if (field->binary() &&
    field->real_type() != MYSQL_TYPE_STRING &&
    field->real_type() != MYSQL_TYPE_VARCHAR &&
    (field->type() != MYSQL_TYPE_FLOAT || field->decimals() == 0))
{
  return !store_val_in_field(field, right_item, CHECK_FIELD_WARN);
}

So my questions are

1. Is it practical to store in indexes string columns, that needed only as data? For example table with 20 columns, and we often need strcolumn, that is searched by intcolumn. Is it good to create index like (intcolumn,strcolumn) or we realy need only (intcolumn) here?

2. Does mysql in innodb engine really does some extra actions for retrieving the data (when we see "Using where; Using index")?

3. Also same happens for ENUMs. It happens, because Enum_field`s real_type returns MYSQL_TYPE_STRING. Does it do same for enums?

4. Can we then assume, that enums is super evil and we should always use just simple reference table instead?

5. For MyISAM it's undertandable, as it stores in index not all values. But then why do it's stores two values -- not one?

6. If this is all really happens -- is it just current restrisctions of mysql kernel, that does not depend of concrete handler implementation?

ps: I see that this question is something huge. If someone will help to reformulate/break it -- it will be nice.

---

Update1: adding another SQL about "Using index" vs "Using index; using where"

@see fiddle at http://sqlfiddle.com/#!8/3f287/2

DROP TABLE IF EXISTS tab;
CREATE TABLE IF NOT EXISTS tab (
    id INT NOT NULL AUTO_INCREMENT
  , num1 TINYINT NOT NULL
  , num2 TINYINT
  , str3 CHAR(1) NOT NULL

  , PRIMARY KEY (id)
  , INDEX num1_num2 (num1, num2)
  , INDEX num1_str3 (num1, str3)
  , INDEX num2_num1 (num2, num1)
  , INDEX str3_num1 (str3, num1)

) ENGINE=InnoDB;
INSERT INTO tab (num1, num2, str3) VALUES
    (1, 1, '1')
  , (2, 2, '2')
  , (3, 3, '3')
  , (4, 4, '4')
  , (5, 5, '5')
  , (6, 6, '6')
  , (7, 7, '7')
  , (8, 8, '8')
  , (9, 9, '9')
  , (0, 0, '0')
;
INSERT INTO tab (num1, num2, str3) SELECT num1, num2, str3 FROM tab;

-- Using index
EXPLAIN SELECT num2 FROM tab WHERE num1 =  5;
EXPLAIN SELECT str3 FROM tab WHERE num1 =  5;
-- Using where; Using index
EXPLAIN SELECT num1 FROM tab WHERE num2 =  5;
EXPLAIN SELECT num1 FROM tab WHERE str3 = '5';

Questions #2

6. Why in case of search by not null int we see just "Using index"?

7. But in case of nullable int OR string -- we see also "Using where"?

8. What additional actions does mysql do there?

Answer 1

1. Is it practical to store in indexes string columns, that needed only as data? For example table with 20 columns, and we often need strcolumn, that is searched by intcolumn. Is it good to create index like (intcolumn,strcolumn) or we realy need only (intcolumn) here?

   This is known as a covering index; it has the performance benefit of being able to retrieve the selected columns from the index file without having to lookup values from amongst the records in the table data.

   As with everything, its use is a tradeoff which may be appropriate in some circumstances but not in others.

2. Does mysql in innodb engine really does some extra actions for retrieving the data (when we see "Using where; Using index")?

   The sqlfiddle to which your question links shows Using where; Using index for all four queries. As documented under EXPLAIN Extra Information:

   The Extra column of EXPLAIN output contains additional information about how MySQL resolves the query. The following list explains the values that can appear in this column.

   [ deletia ]

   • Using index

     The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

     If the Extra column also says Using where, it means the index is being used to perform lookups of key values. Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups.

   Therefore, all of your queries are using covering indexes for both lookups and data retrieval, irrespective of the storage engine that is used.

   It is not clear to me to what you are referring when you say "innodb engine really does some extra actions for retrieving the data". The only difference in the EXPLAIN output that I can see is that the InnoDB queries show a lower value in the Rows column; however, as documented:

   The rows column indicates the number of rows MySQL believes it must examine to execute the query.

   For InnoDB tables, this number is an estimate, and may not always be exact.

3. Also same happens for ENUMs. It happens, because Enum_field`s real_type returns MYSQL_TYPE_STRING. Does it do same for enums?

   Again, it is not clear to me to what you are referring when you say that "same happens". However, as discussed above, Using where; Using index merely indicates that a covering index has been used both for lookups and for data retrieval.

   Furthermore, ENUM fields have a real_type of MYSQL_TYPE_ENUM, not MYSQL_TYPE_STRING. See sql/field.h:1873:

     enum_field_types real_type() const { return MYSQL_TYPE_ENUM; }
   

4. Can we then assume, that enums is super evil and we should always use just simple reference table instead?

   There are many reasons to avoid ENUM, but I don't think your question has touched on any of them.

5. For MyISAM it's undertandable, as it stores in index not all values. But then why do it's stores two values -- not one?

   The egrep results are leading you to draw false conclusions. Just because a case-insensitive search for the pattern "tobeornottobe" finds two matching strings in the .myi file does not mean that the MyISAM index has two records. The data structure is a tree, as follows:

                 /\
                /  \
   Tobeornottobe    toBeornottobe
                      /\
                     /  \
        tobeOrnottobe    tobeorNottobe
                          \
                           \
                            tobeornotTobe
   

   One gets a hint of this from viewing all strings .myi index file:

   $ strings mysql_index_reading_myisam.MYI
   Tobeornottobe
   toBeornottobe
   beOrnottobe
   orNottobe
   notTobe
   

   Therefore, had you performed a (case insensitive) search for the pattern "nottobe", you would have found five matches rather than two.

   You can read more about the storage format of MyISAM's index structures in The .MYI File.

6. If this is all really happens -- is it just current restrisctions of mysql kernel, that does not depend of concrete handler implementation?

   I'm afraid that I have no idea what is being asked here.