need some help setting up a database index

[scratch]

i basically understand indexes, but want to have an index help with an OR in the search.

my query is

SELECT id, title, thumbnail, fromid FROM library WHERE fromobject='Image' AND ( userid ='505' OR contractid = '199' )

i want it to use an index to do the search
have seperate indexs on each field in query, and tried doing
CREATE INDEX fromobject ON library (fromobject, userid, contractid);

it is only using fromobject for the query ( which has the same value for 99% of the database which does me no good )

what is the best way to set this up so it uses all the fields in the index

[zCat]

Which database?

Probably you'll need two indexes:

CREATE INDEX fromobject ON library (fromobject, contractid);
CREATE INDEX fromobject ON library (fromobject, userid);

How they are used will depend on the DB's planner.

[zCat]

Although if "fromobject" is the same in 99% of cases, indexes on "userid" and "contractid" should do the trick as well.

[scratch]

mysql 4.1

fromobject is "Image" for about 40,000 records, it is "Font" and "Color" in about 200 or so records. Main reason it is there is so i only get the image records.

had to change the index names in your suggestion, and i added more to try to get this to work(trial and error)... these are my current indexes

KEY `defaultSetOpt` (`fromobject`),
KEY `session` (`sessid`),
KEY `user` (`userid`,`contractid`),
KEY `contractid` (`contractid`),
KEY `fromobject` (`fromobject`,`contractid`),
KEY `fromobject2` (`fromobject`,`userid`),
KEY `fromobject3` (`contractid`,`fromobject`),
KEY `fromobject4` (`userid`,`fromobject`)

i ran
explain SELECT id, title, thumbnail, fromid FROM library WHERE fromobject='Image' AND ( userid ='505' OR contractid = '199' )

and got
type=simple
table=library
type=ALL
possiblekeys= all of em above
key=NULL
key_len=NULL
ref=NULL
rows=42306
Extra=using where

explain SELECT id FROM library WHERE ( contractid = '199' AND fromobject='Image' ) OR ( userid ='505' AND fromobject='Image' )
also is the same basically

i think im trying to get rows to be a smaller number, it is fast, but the record count is going to be going up...

I think it hits the OR and it stops working, putting in an AND instead and it works fine...