List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:February 17 2000 7:20pm
Subject:Re: Index on partial DATETIME/TIMESTAMP field (like partial char field)
View as plain text  
>>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:

Tim> On Thu, Feb 17, 2000 at 05:01:04PM +0200, Michael Widenius wrote:
>> 
>> >>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:
>> 
Tim> In the MySQL docs it says:
Tim> : For `CHAR' and `VARCHAR' columns, indexes can be created that use only
Tim> : part of a column, using `col_name(length)' syntax.  (On `BLOB' and
Tim> : `TEXT' columns the length is required). The statement shown below
Tim> : creates an index using the first 10 characters of the `name' column:
Tim> : 
Tim> :     mysql> CREATE INDEX part_of_name ON customer (name(10));
Tim> : 
Tim> : Since most names usually differ in the first 10 characters, this index
Tim> : should not be much slower than an index created from the entire `name'
Tim> : column.  Also, using partial columns for indexes can make the index
Tim> : file much smaller, which could save a lot of disk space and might also
Tim> : speed up `INSERT' operations!
>> 
Tim> Are there any plans to do something similar for DATETIME/TIMESTAMP fields?
>> 
Tim> If not, could it be added to the to-do list?
>> 
>> DATETIME takes only 8 bytes and timestamp 4;  I can't really see a
>> reason for just indexing a couple of these bytes (at least not as
>> these are packed and the a prefix of these are pretty useless).
>> 
>> Or did you mean something else?

Tim> I'm assuming that if I have many rows per day (say 1 million) in a
Tim> table that includes a timestamp field, and that timestamp field was
Tim> indexed, then using a partial index on just the date part of the
Tim> timestamp would save index space (because many rows would share the
Tim> same index entry).

Timestamp is saved as seconds since 1970.01.01;  It would be pretty hard to
add a partial index on this.

Regards,
Monty
Thread
Index on partial DATETIME/TIMESTAMP field (like partial char field)Tim Bunce17 Feb
  • Index on partial DATETIME/TIMESTAMP field (like partial char field)Michael Widenius17 Feb
    • Re: Index on partial DATETIME/TIMESTAMP field (like partial char field)Tim Bunce17 Feb
      • Re: Index on partial DATETIME/TIMESTAMP field (like partial char field)Michael Widenius17 Feb
        • Re: Index on partial DATETIME/TIMESTAMP field (like partial char field)Tim Bunce18 Feb