Mysql – Will a covering index save a table hit if only the commoly-used length of a `text` field is indexed

indexindex-tuningMySQL

I would like to add a covering index for a table that contains a text field. The maximum size of the field that is in use is ~2000 ASCII characters, 95% is below 800 characters. I am considering either not adding this field to the covering index, or indexing only the first 800 characters.

When retrieving rows from this table, will MySQL know that the matched rows have values that fit inside the indexed portion? If this is so, then I'll just index the first 800 characters and for most queries MySQL will not have to hit the table.

If MySQL does not know that the entire field was indexed for all matched rows, and thus must hit the table for this field anyway, am I doing any good with having the other SELECTed fields in the covering index?

These are my options:

# baz is a text field
SELECT foo, bar, baz FROM users WHERE id=42;

# If MySQL will know that the matched rows have values that
# fit inside the indexed portion
ALTER TABLE users ADD INDEX (id, foo, bar, baz(800));

# If MySQL will not know that the matched rows have values
# that fit inside the indexed portion and indexing the
# other SELECT fields helps
ALTER TABLE users ADD INDEX (id, foo, bar);

# If MySQL will not know that the matched rows have values
# that fit inside the indexed portion and indexing
# the other SELECT fields does not help
ALTER TABLE users ADD INDEX (id);

Best Answer

It depends.

  • Prefix indexing INDEX baz(800) is rarely useful.
  • MyISAM and InnoDB work dramatically differently in the area you are in.
  • In InnoDB, if id is the PRIMARY KEY, it is almost useless to have an index starting with id. This is because the PK is clustered with the data. For MyISAM, the PK is a separate BTree, so the analysis is quite different.
  • If you are using InnoDB, which version, etc, is the table? Antelope, ... COMPRESSED, ...?
  • Adding extra indexes adds more stuff to cache, thereby possibly slowing down other queries by bumping things out of cache. Since your 'optimization', if it works, is not likely to save more than one disk hit, you may not actually be speeding up the system any.
Related Question