Mysql – Performance impact of including result column in indexed columns

index-tuningMySQLperformancequery-performance

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.

Best Answer

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.

Related Solutions

Mysql – How to optimize indexes on MySQL query with various sorts

WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()

Start with the "=" item, then do the range:

INDEX(playable_character, date_published);

"Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you don't need.

Mysql – Identical query, tables, but different EXPLAIN and performance

Just make a simple join. Sub-queries does not provide the best result quite often

EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating
FROM levels AS l
INNER JOIN users_favorites AS uf 
ON uf.level_id = l.id
WHERE l.user_id = 2;

Best Answer

Related Solutions

Mysql – How to optimize indexes on MySQL query with various sorts

Mysql – Identical query, tables, but different EXPLAIN and performance

Related Question