List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 28 2006 9:08am
Subject:Re: Index effectivity for a 2-valued field (was Re: Should I use an index in this case?)
View as plain text  
Hi,

I'm not a wizard on the MySQL optimizer, but here's my go...

> > 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 guess it depends on what value you are querying for :)

Meaning:
out of 1,000,000 rows, if there's 1000 with a value
of "preview", it should be able to use the index and
fetch the 1000 rows. The other way around, meaning
999,000 rows, it should not use the index, as it wouldn't
make sense.

But hey, that's up to MySQL to decide.

The easiest thing to do is to create a table with a lot
of rows, try your queries with and without indices and
read the query plan.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
 
> -------------------------------------------------
> 
> 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