join - the role of index in performance in mysql -

why defining index mysql tables increase performance in queries haveing join?

if interested in specific topic in book, go of book , find alphabetically in index. index tells page number(s) topic discussed. jump straight pages interested in. much, faster reading whole book.

it's same in database. index means can jump joining rows instead of scanning every row in table looking match.

have @ how clustered index works (http://msdn.microsoft.com/en-us/library/ms177443.aspx). can have 1 of per table.

this artical explains how non clustered index works (http://msdn.microsoft.com/en-us/library/ms177484.aspx). can have many of them want.

both of these articles microsoft sql server, theory behind indexes same across relational database management systems.

indexes have associated cost. every time insert/update performed on table, effected index(es) may have updated also. , of course indexes take space - not issue of us. need balance performance benefits of faster joins or filtering against costs of inserts , updates.

as guide, want index matches each of columns included in join or clause:

select      *       customer       registrationdate > @registrationdate      , registrationcountry = @registrationcountry; 

so index on customer table includes registrationdate , registrationcountry columns speed query. since using ">" in our query, candidate clustered index (the first article shows clustered index physically arranges data in index order range queries can isolate range of index).

select      *       customer c     inner join order o         on o.customerid = c.customerid         , o.ordertype = @ordertype 

here, want index on customer table contains customerid column. , we'd want index on order table contains customerid , ordertype columns. both sides of join not need table scan.

typically there small number of ways data queried table, won't index overload. lots of indexes sign tables have mixed concerns , normalized.