Re: How can a single row with a single column represent 2 values without bitwise data representation
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 1 = 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]
How can a single row with a single column represent 2 values without bitwise data representation
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?
Re: How can a single row with a single column represent 2 values without bitwise data representation
On Fri, 27 Jun 2003 14:50:58 -0700, Dathan Vance Pattishall [EMAIL PROTECTED] wrote: | 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? Try using a SET column type and then using mysql's FIND_IN_SET() function in your where clause. -- Michael Brunson 504.200. [EMAIL PROTECTED]ICQ: 83163789 Data Center Manager -- www.zipa.com --Zipa, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can a single row with a single column represent 2 values without bitwise data representation
On Fri, Jun 27, 2003 at 05:27:02PM -0500, Michael Brunson wrote: On Fri, 27 Jun 2003 14:50:58 -0700, Dathan Vance Pattishall [EMAIL PROTECTED] wrote: | 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? Try using a SET column type and then using mysql's FIND_IN_SET() function in your where clause. Excepet that he wants to receive the benefits of an index in this case. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 24 days, processed 778,166,849 queries (365/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can a single row with a single column represent 2 values without bitwise data representation
Dathan Vance Pattishall wrote: Say you have a column in a search table by the name of politics. 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? I don't think that there's a perfect answer for your problem. One possibility would be to break the values out into multiple columns, one for each view, and have indexes on each of those columns, or at least the views that you consider most important. It might help to make them compound indexes, with each including several views that you think are likely to be searched on together. Another, more normalized, approach would be to have two tables -- one for each user, and one for each view held by a user (encoded 1-16 if you like). This would have the advantage of being more readily extendable if you decide that there are more political views that you'd like to track in the future. However... aren't you worried that a 'politics' table is likely to be corrupt? g Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can a single row with a single column represent 2 values without bitwise data representation
---Original Message- --From: Bruce Feist [mailto:[EMAIL PROTECTED] --Sent: Friday, June 27, 2003 3:51 PM --To: Dathan Vance Pattishall --Subject: Re: How can a single row with a single column represent 2 values --without bitwise data representation -- --Dathan Vance Pattishall wrote: -- -- --Say you have a column in a search table by the name of politics. -- -- -- --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? -- -- --I don't think that there's a perfect answer for your problem. One --possibility would be to break the values out into multiple columns, one --for each view, and have indexes on each of those columns, or at least --the views that you consider most important. It might help to make them --compound indexes, with each including several views that you think are --likely to be searched on together. Compound indexes do not work with the OR sql statement across different columns. Doing the bitwise query essentially allows for the OR. If I'm wrong please some one let me know, I haven't seen it work yet. Also imagine using this approach on 16 different questions-politics, social background, religion, etc. This would be 16*32 columns such that 32 is the number of possible answers for each of the 16 questions. -- -- --However... aren't you worried that a 'politics' table is likely to be --corrupt? g Funny -- --Bruce Feist -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can a single row with a single column represent 2 values without bitwise data representation
Bruce Feist wrote: One --possibility would be to break the values out into multiple columns, one --for each view, and have indexes on each of those columns, or at least --the views that you consider most important. It might help to make them --compound indexes, with each including several views that you think are --likely to be searched on together. Dathan Vance Pattishall wrote: Compound indexes do not work with the OR sql statement across different columns. Doing the bitwise query essentially allows for the OR. If I'm wrong please some one let me know, I haven't seen it work yet. True, but I believe you can get around that by doing UNIONs instead of ORs. Also imagine using this approach on 16 different questions-politics, social background, religion, etc. This would be 16*32 columns such that 32 is the number of possible answers for each of the 16 questions. Ah, I didn't realize that these weren't yes/no issues. If there are 32 possible answers for each of the questions, won't that prevent you from using the bitwise structure you described in any case? --However... aren't you worried that a 'politics' table is likely to be --corrupt? g Funny Sorry -- I couldn't resist! Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
