[SQL] Table indexes in a SELECT with JOIN´s

[JORGE MALDONADO]

Let´s suppose that I have a SELECT statement that joins more than one table
and such a statement is order by fields that belong not only to the table
in the FROM but also by fields in the tables that are part of the JOIN´s.
How does indexes should be considered in a case like this? For example:

SELECT artist_name, author_name, producer_name, song_name
FROM tbl_songs
INNER JOIN tbl_artists ON tbl_artists.artistid = tbl_songs_artistid
INNER JOIN tbl_authors ON tbl_authors.authorid = tbl_songs_authorid
INNER JOIN tbl_producers ON tbl_producers.producerid = tbl_songs_producerid
ORDER BY song_name

Respectfully,
Jorge Maldonado

[SQL] Re: [SQL] Table indexes in a SELECT with JOIN´s

[Pavel Stehule]

Hello


2013/4/20 JORGE MALDONADO 

> Let´s suppose that I have a SELECT statement that joins more than one
> table and such a statement is order by fields that belong not only to the
> table in the FROM but also by fields in the tables that are part of the
> JOIN´s. How does indexes should be considered in a case like this? For
> example:
>
> SELECT artist_name, author_name, producer_name, song_name
> FROM tbl_songs
> INNER JOIN tbl_artists ON tbl_artists.artistid = tbl_songs_artistid
> INNER JOIN tbl_authors ON tbl_authors.authorid = tbl_songs_authorid
> INNER JOIN tbl_producers ON tbl_producers.producerid = tbl_songs_producerid
> ORDER BY song_name
>
>

It depends on size of relations - you don't need indexes on small tables -
hash join will be used. For bigger tables indexes on PK (automatically) and
FK are good idea.

Regards

Pavel



> Respectfully,
> Jorge Maldonado
>