0

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);
1

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.
  • Thank you Rick. In fact this is an InnoDB table (probably Antelope). – dotancohen Feb 10 '15 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.