> So, my question is how can multiple values for a single > field get represented in a single row and receive the > benefits of an index? How can this data be represented?
Nice puzzle. At first I thought it couldn't be done, given all your constraints. But there is one way. You will have to build your own index. For each row, you will need to build a separate index entry for each possible subset of that row's views. If a row subscribed to views 5 and 2, for example, you would need three entries: 10010 = 18 10000 = 16 00010 = 2 If someone subscribed to all 16 views, you would need -- let me see -- 65,536 index entries. That solves your problem as you've presented it to us. (Do I get a prize?) P.S. There might be a better way to solve the actual problem. Perhaps you should rethink your constraints. Maybe allow us more than one column? ...more than one table? ...more than one index? Cheers, John For example, in your example row On Friday 27 June 2003 05:50 pm, Dathan Vance Pattishall wrote: > Say you have a column in a search table by the name of > politics. > > Each political view is represented as an integer from 0 - > 16. A user can choose multiple political views, and those > views have to be represented in the db as a single row. > How can a single column for a single row of a searched > table represent multiple values while using an index? > > Current I convert the integer value 0-16 into a bitwise > representation. > > So if a user picks values 2,5 the integer entered into > the db, for the users row for column politics as > > 18 (this is the result of the bitwise manipulation) > > Thus when searching for a users politics and you want > users who have political views of 2,5,7, or 9 show up in > your search a query needs to be done in this manner using > the approach above. > > SELECT * from searched_table where politics & 338; > > What is the problem here? > An index is not available when using bitwise arithmetic > on tables so a resulting table scan occurs. > > So, my question is how can multiple values for a single > field get represented in a single row and receive the > benefits of an index? How can this data be represented? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
