Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Please post sample queries you use to retrieve texels. Also, please run SHOW CREATE TABLE texeltblname\G and show its result. –  RolandoMySQLDBA Jan 1 '13 at 1:58
    
Please tell us if the table is MyISAM or InnoDB. –  RolandoMySQLDBA 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? –  hexafraction 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. –  RolandoMySQLDBA Jan 1 '13 at 2:17
    
I added an answer. Hope it helps !!! –  RolandoMySQLDBA Jan 1 '13 at 2:39
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
Oh, even before I have working SQL, that answers all I needed. Thanks! –  hexafraction Jan 1 '13 at 3:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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