Thread: indexing

indexing

From
"James Cooper"
Date:
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?

Re: indexing

From
Josh Berkus
Date:
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