"Gordon Bruce" <[EMAIL PROTECTED]> wrote on 08/26/2005 05:04:17 PM:
> It's getting late on Friday, but couldn't you build a table with all of > the parameter combinations and then just join against that table? > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, August 26, 2005 4:25 PM > To: mysql@lists.mysql.com > Subject: Union vs OR > > I have a table that holds attributes for users. This is the structure: > > TABLE properties ( > id int(11) NOT NULL, > userid int(11) NOT NULL, > attrType int(11) NOT NULL, > attrValue text NOT NULL, > FULLTEXT KEY propValue (propValue) > ) TYPE=MyISAM; > > The table is used to find people based on criteria. > > A simple query: > > select > userID, attrType, attrValue from properties > where > propType = 1 > and > propValue= 'some value' > > The problem I'm running into is that the number of attributes could be > over > 50. > > Would a query with many sets of > > (propType = 1 and propValue= 'some value') > or > (propType = 2 and propValue= 'some other value') > or ... > > work better than doing the same thing with unions? > > Or does anyone have an alternate solution? > > Thanks for any help! > > -- Avi > I think Gordon's suggestion has merit. Create a temporary table like CREATE TEMPORARY TABLE tmpSearch ( PropType , PropValue , KEY(PropType, PropValue) ); and populate it with the list of search parameters INSERT tmpSearch(Proptype, propvalue) VALUES (1,'some value'),(2,'some other value'),(2,'an alternative to some other value'); Then JOIN this table to your data: SELECT ... FROM properties p INNER JOIN tmpSearch ts ON ts.PropType = p.PropType AND ts.PropValue = p.PropValue; That will give you a list of all property records that match your search conditions (the same thing you would have had with the OR-ed query). If you don't want to do this, I think the UNION form would probably make better use of any indexes. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
