defined indexes for Columns used a) in Joins b) in Search conditions..

P K5y ago

How indexes when Joins ,in Search conditions,Order by Clause?

+1

no mainly when you have a requirement to query based on foreign key , if indexes are created correctly it is helpful otherwise it will be even more painful, be very cautious when creating indexes.

+1

P K5y ago

create composite keys, is it applicable all cases when create table?

+1

Sachin Singh5y ago

you need to create composite keys and then you can define indexes, now when you will join and search based on these columns it should be comparitively better.

CREATE TABLE albums_artists
( album_id integer NOT NULL REFERENCES albums
, artist_id integer NOT NULL REFERENCES artists
, PRIMARY KEY (album_id, artist_id)
);
CREATE UNIQUE INDEX ON albums_artists (artist_id, album_id);

+1