>From what I know, an index on a field that can only have two values will never be used, except to make INSERTs slower.
By the way, I think I remember that you have a VARCHAR in those records. Doesn't that force the CHAR to become a VARCHAR under the hood? That might be less efficient that using some other data type. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Andy Sy [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 28, 2006 4:05 AM > To: mysql@lists.mysql.com > Cc: [EMAIL PROTECTED] > Subject: Index effectivity for a 2-valued field (was Re: > Should I use an index in this case?) > > [EMAIL PROTECTED] wrote: > > > > Hi Andy, > > > > Two questions for you: > > 1) Why store so many repeatable data in the same table? > > I mean you needn't to store "purchases"/"previews" for all records. > > You can choose MySql SET datatype, or you can choose > another table to > > store the action types and let the original table refers to it. > > So that you can reduce the diskspace and also the I/O. > > Hmmm... not sure if the SET datatype is portable > across databases, is it? > > Also, remember there will only be 2 action > types: 'purchase' and 'preview'. > > Name (of visitor) will actually be an integer id, > and I could use a bool or an int for 'action' also, > since it is intended to be 2 valued. But a diff > of 1 or 2MB of storage is negligible (around extra > 9 bytes per row for the 'action' field), and I want > the flexibility and readability of using the char > field. > > > The important concept here is the TIMESTAMP, each > access/visit needs to be recorded separately and > thus cannot be normalized away. So if there are > a hundred thousand visits, there NEEDS to be a > hundred thousand rows. > > > I guess the question probably boils down to: > > "If a field (of whatever type) will only ever have > 2 values, will indexing it bring benefits when said > field is being refered to in a WHERE clause for a > very large table?" > > > > ------------------------------------------------- > > I just realized I could do 2 tables, one that > stores purchase 'visits' and another that stores > 'preview' visits, but that complicates the design > and I don't think that's a good idea. Hundreds > of thousands of rows is not that hard for MySQL > to deal with, but what I want to understand is > if I make an index for 'action', will the performance > diff be on the order of, say, a query taking 2 > seconds versus 0.5 seconds? > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
