List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:November 28 2006 3:55pm
Subject:RE: Index effectivity for a 2-valued field (was Re: Should I use an index in this case?)
View as plain text  
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: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
> 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/mysql?unsub=1
>
>



Thread
Should I use an index in this case?Andy Sy28 Nov
  • Re: Should I use an index in this case?Yonghua.Wu28 Nov
    • Index effectivity for a 2-valued field (was Re: Should I use an indexin this case?)Andy Sy28 Nov
      • RE: Index effectivity for a 2-valued field (was Re: Should I use an index in this case?)Jerry Schwartz28 Nov
    • Re: Index effectivity for a 2-valued field (was Re: Should I use an index in this case?)Martijn Tonies28 Nov