Performance impact of including result column in indexed columns

Question

I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of:

texelid INT (PK AI NN)
texelx TINYINT (NN)
texely TINYINT (NN)
texelhue INT (NN)
identifiedtex INT (NN)

Identifiedtex is a foreign key in a 1:N relation of one identifiedtex to many texels(texture elements)

If I create an index(B-tree) using texelx, texely, and texelhue then I can quickly find a row. However, if I add identifiedtex as the last column indexed in the index, will this speed up a lookup of the texture since the result is part of the index? Just as a note, a combination of texelx, texely, and texelhue will result in multiple identifiedtex's, and a few different rows will need to be looked up to identify the texture.

I plan to identify textures by using an inner join against the same table(self join) in order to find rows that have a matching X, matching Y, and matching hue, and grouping by distinct identifiedtex's.

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

Based on the information given, I will take a shot at an answer...

You stated

If I add identifiedtex as the last column indexed in the index, will this speed up a lookup of the texture since the result is part of the index?

Let me say that you have the following query:

SELECT identifiedtex FROM texels
WHERE texelx = 210938
AND texely = 378432
AND texelhue = 23;

What would be the effect of having an index (call it Index3)

texelx, texely, texelhue

versus this index (call it Index4)

texelx, texely, texelhue, identifiedtex

When you retrieve identifiedtex using only Index3, it will require an additional table lookup to ascertain the identifiedtex from the table.

When you retrieve identifiedtex using only Index4, it will an index-only lookup because all mentioned columns are in the index. It is known as a covering index.

I mentioned covering indexes in my past posts:

Mar 12, 2012 : How to index this table (a_level, b_level, item_id)
Oct 17, 2012 : Combining columns in index
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY? (See My Conclusion)

So to answer your original question, yes adding identifiedtex will index make for a faster query.

Performance impact of including result column in indexed columns

1 Answers1

Linked