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.
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -----Original Message-----
> From: Andy Sy [mailto:andy@stripped]
> Sent: Tuesday, November 28, 2006 4:05 AM
> To: mysql@stripped
> Cc: Yonghua.Wu@stripped
> Subject: Index effectivity for a 2-valued field (was Re:
> Should I use an index in this case?)
> Yonghua.Wu@stripped 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
> 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: