Thread: Table indexes in a SELECT with JOIN´s
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
Hello
2013/4/20 JORGE MALDONADO <jorgemal1960@gmail.com>
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_nameFROM tbl_songsINNER JOIN tbl_artists ON tbl_artists.artistid = tbl_songs_artistidINNER JOIN tbl_authors ON tbl_authors.authorid = tbl_songs_authoridINNER JOIN tbl_producers ON tbl_producers.producerid = tbl_songs_produceridORDER 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
Pavel
Respectfully,Jorge Maldonado