Thread: indexing
Hi all,
I've been doing a little reading on indexing in prelude to indexing my db.
I have the following to ask:
if I had three tables for a many to many relationship say A, B, AND C
B being the lookup. B being a huge 50k rows plus column and made just two forigen keys(b.a_id,b.c_id).
is it best to create two non-unique indexes or one unique index on both fields?
Please advise
Ps
I also attempted creating an index on a table i have called person on person_id and
ran
Explain
select person_id from person where person_id < n
but saw no results of my created index being used - am i doing something incorrectly
Pps
When indexing if searching tables is more important than concurrency - which type of index is best?
James, > if I had three tables for a many to many relationship say A, B, AND C > B being the lookup. B being a huge 50k rows plus column and made just two forigen keys(b.a_id,b.c_id). > is it best to create two non-unique indexes or one unique index on both fields? That depends on whether all three tables are usually queried toghether. If yes, then a 2-column index is probably better. If not, use single-column indexes. If you want more specific advice, post your table structures. > I also attempted creating an index on a table i have called person on person_id and > ran > Explain > select person_id from person where person_id < n > > but saw no results of my created index being used - am i doing something incorrectly Not a surprise. If your the planner expects person_id < n to return a significant portion of the table, a table scan is faster than an index scan. > Pps > When indexing if searching tables is more important than concurrency - which type of index is best? You want to use a B-tree index for anything other than statistical and/or geometic data. You are unlikely to need any other kind of index. -- -Josh BerkusAglio Database SolutionsSan Francisco