List:General Discussion« Previous MessageNext Message »
From:Andy Sy Date:November 28 2006 9:04am
Subject:Index effectivity for a 2-valued field (was Re: Should I use an index
in this case?)
View as plain text  
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?

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