database - about indexes created on primary keys referenced as foreign keys in a different table (SQLite3) -

scenario database design follows: people visit matchmakers network each other , propose matches. example, person visits matchmaker x, , person b visits matchmaker y, not equals b , no constraint on x, y i.e. can same or different.

create table matchmaker ( id text primary key, address text );  create table people ( id text primary key, name text, gender text, matchmaker_id text,     foreign key(matchmaker_id) references matchmaker(id));  create table married_couples ( id1 text, id2 text,     foreign key (id1) references people(id),      foreign key (id2) reference people(id)); 

then, faster database access:

create index matchmaker_index on matchmaker(id); create index people_index on people(id); 

my question based on following query generate tuples of matchmaker pairs people they've paired.

select a.id, b.id, e.id1, e.id2 matchmaker a, matchmaker b,  people c, people d,  married_couples e e.id1 = c.id , c.id = a.id ,  e.id2 = d.id , d.id = b.id; 

for query above, 2 matchmaker_index , people_index suffice or, there need 2 more (other) indexes below?

create index matchmaker_people_index on people(id, matchmaker_id); create index married_couples_index on married_couples(id1, id2); 

additional info:

1) matchmaker has 20074 unique entries;

2) people has 20494819 unique entries;

3) married_couples ?? (i don't have information yet, it's going big)

also, it's possible married_couples have duplicate entries. so, after creating relevant indexes, run query delete duplicates below:

delete married_couples  rowid not in ( select min(rowid)     married_couples     group id1, id2); 

sqlite generates indexes automatically columns declared primary key or unique. faq, see question 7. these 2 indexes

create index matchmaker_index on matchmaker(id); create index people_index on people(id); 

are duplicates. drop them.

this index

create index matchmaker_people_index on people(id, matchmaker_id); 

might help.

also, it's possible married_couples have duplicate entries.

remove possibility. add primary key and check constraint.

create table married_couples (    id1 text, id2 text,     foreign key (id1) references people(id),      foreign key (id2) references people(id),     primary key (id1, id2),     check (id1 < id2) ); 

the primary key provides index, too.

when you're talking marriages, marriage between mike , mindy duplicate of marriage between mindy , mike. check constraint prevents kind of subtle duplication. prevents people marrying themselves.

i'm not sure might want woman married eiffel tower.

you can simplify original query quite lot. learning ansi joins idea.

select id1, id2, c.matchmaker_id m_id1, d.matchmaker_id m_id2 married_couples inner join people c   on c.id = a.id1 inner join people d   on d.id = a.id1;