Re: Index/Foreign Key Question

On Fri, 2003-10-10 at 16:04, David Busby wrote:
> List,
>     I'm creating this multi company POS database.
> My inventory table looks like (all items are unique):
>
> id,category_id,invoice_id,x,y,z,gid,uid
>
> I have a primary key on id, and then an foreign keys on category_id and
> invoice_id.
> GID is the group ID of the company, UID is the companies user, they are also
> connected via foreign key to the respective tables.  My question is this: Do
> I need to create more indexes on this table when inventory selects look like
>
> select * from inventory where
>  category_id = 1 and invoice_id is null and gid = 2
>
> So where would the indexes need to be placed?  Or since I have the FK setup
> are the indexes already in place?  I expect to soon have >500K items in the
> inventory table and don't want it to slow down.  I'll have the same type of
> issue with clients, invoices, purchase_orders and perhaps more

I'd make a multi-segment (non-unique?) index on:
   GID
   CATEGORY_ID
   INVOICE_ID

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

LUKE: Is Perl better than Python?
YODA: No... no... no. Quicker, easier, more seductive.
LUKE: But how will I know why Python is better than Perl?
YODA: You will know. When your code you try to read six months
from now.