Multi Table Query

[mike]

I have two tables which I want to query off of. The relationship is one to many.

For Example:

A (car) has many (parts)

On the car table I have id as primary key and an index on color.

On parts, I have carid indexed along with price.

The actuall tables I am using have 20 million rows and 80 million rows

Now if I do a query on:

Select car.* from car, parts where car.id = parts.carid AND car.color = 'red' and parts.price > 800

Is there anyway I can utilize the color index and also the price index? Right now it looks like it will use only one index for the query and then uses the other to link up the two tables. So I guess it is first getting all cars which are red (using an index) and then looking to see if they have any parts which are greater than 800, search through cars that are red but maybe some with no parts over 800.

I also wrote the query as:
select car.* from car where car.id = (select carid from parts where parts.carid = car.id)
which is referencing the outer query from the inner query but that still searches through every car that is red.

Any help would be appreciated.

Thanks!