[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]
