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.

  • 1
    Please post sample queries you use to retrieve texels. Also, please run SHOW CREATE TABLE texeltblname\G and show its result. Jan 1 '13 at 1:58
  • Please tell us if the table is MyISAM or InnoDB. Jan 1 '13 at 1:59
  • @RolandoMySQLDBA I'm still in the design phase on paper, unfortunately, since there are many other factors outside database design that need to go into the finished product. I'll try to add any info that I can. Was it too early to ask without this info?
    – nanofarad
    Jan 1 '13 at 2:05
  • Yes. When I see a query, the WHERE, GROUP BY and ORDER BY clauses usually give clues as to what indexes to create. Jan 1 '13 at 2:17
  • I added an answer. Hope it helps !!! Jan 1 '13 at 2:39

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:

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

  • Oh, even before I have working SQL, that answers all I needed. Thanks!
    – nanofarad
    Jan 1 '13 at 3:25

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.