mysql query and index

Question

Does this query will be faster with a index on "t.type1" and "x.type1" or only index on "x.type1" is enought?

SELECT t.id, x.id
FROM t
INNER JOIN x ON x.type1=t.type1
WHERE t.id=1

Have you tried? A better question would have been: "I've made a small benchmark and found that this query is slower if I change indexes. Why is that?" — Tomalak
– Tomalak, Commented Jun 6, 2010 at 14:15
How many rows does your table have? How many rows will typically have t.id=1? — Mark Byers
– Mark Byers, Commented Jun 6, 2010 at 14:16

Mark Byers · Accepted Answer · 2010-06-06 14:13:28Z

2

You should have an index on t.id (presumably it is the primary key?) and an index on x.type1.

answered Jun 6, 2010 at 14:13

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Heikens · Accepted Answer · 2010-06-06 14:13:27Z

1

It depends, how many records do you have and how many unique values you have in these columns? Just use EXPLAIN to see what the database does and do this with and without indexes. You'll see the difference.

answered Jun 6, 2010 at 14:13

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

2 Comments

parm.95 Over a year ago

Thanks for you reply Frank. I thought Mysql fetch rows from table "t" which are corresponding to the "WHERE" clause and after this join table "x" on the "t.type1" value, so "t.type1" doen't need to be an index. Could you told how Mysql proceed with this query?

Martin Smith Over a year ago

@parm.95 use EXPLAIN to find this out!

Collectives™ on Stack Overflow

mysql query and index

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related