List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:April 12 2007 11:12am
Subject:Re: Can I create a index on a column of Type datetime?
View as plain text  
You should probably create two separate indexes, one on source_url and one on create_date.
Unless you are frequently searching on 
both source_url and create_date.
Remember that an index is really just sorted data that can be searched quickly.Since
create_date will have pretty unique values, it 
would be useless to sort on create_date then source_url since almost not source_url would
have the same create_date.
But if you first sort on source_url, then create_date (create_date last field in the
index), that would be usefull if that's one of 
your search patterns.

I usually create two fields, create_date and create_time. Then I can use just the
create_date as part of a compound index, which 
wouldn't be unique.

----- Original Message ----- 
From: "wangxu" <wangxu@stripped>
To: "Brent Baisley" <brenttech@stripped>
Cc: <mysql@stripped>
Sent: Thursday, April 12, 2007 10:13 AM
Subject: Re: Can I create a index on a column of Type datetime?


> Thank you,
> what 's the meaning of "using a datetime field unless it's the last
> field in the index."?
>
>
> the below is the desc result;Should I create an index on
> (source_url,create_date)?
> for these two fields are to be often queried by.
> +---------------+--------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +---------------+--------------+------+-----+---------+----------------+
> | id | bigint(20) | NO | PRI | NULL | auto_increment |
> | create_date | datetime | NO | | | |
> | brand | varchar(255) | YES | | NULL | |
> | category | varchar(255) | YES | | NULL | |
> | description | text | YES | | NULL | |
> | detailed_desc | text | YES | | NULL | |
> | imageUrl | varchar(255) | YES | | NULL | |
> | name | varchar(255) | YES | | NULL | |
> | price | varchar(255) | YES | | NULL | |
> | properties | text | YES | | NULL | |
> | sku | varchar(255) | YES | | NULL | |
> | source_url | varchar(255) | YES | | NULL | |
> | link_path | varchar(255) | YES | | NULL | |
> | site_name | varchar(255) | YES | MUL | NULL | |
> | page_score | float | YES | | NULL | |
> | tag | varchar(255) | YES | | NULL | |
> | dumped | bit(1) | YES | | NULL | |
> +---------------+--------------+------+-----+---------+----------------+
>
>
> Brent Baisley wrote:
>> You can create an index on just about any field. Just remember that
>> the data in a datetime field is fairly unique, so it would do much
>> good to create a compound index (2 or more fields) using a datetime
>> field unless it's the last field in the index.
>>
>> ----- Original Message ----- From: "wangxu" <wangxu@stripped>
>> To: <mysql@stripped>
>> Sent: Wednesday, April 11, 2007 8:08 PM
>> Subject: Can I create a index on a column of Type datetime?
>>
>>
>>> I will very often do query like this:
>>>
>>> select * from mytable where create_date >='...' and create_date
> <='....'
>>>
>>> can I create a index on the column create_date?
>>>
>>> what is the best practise?
>>>
>>> Thanks,
>>> shell.
>>>
>>>
>>> -- 
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>>
>>
>>
>>
> 

Thread
Can I create a index on a column of Type datetime?wangxu11 Apr
  • Re: Can I create a index on a column of Type datetime?Martijn Tonies11 Apr
    • Re: Can I create a index on a column of Type datetime?wangxu12 Apr
  • Re: Can I create a index on a column of Type datetime?Brent Baisley12 Apr
Re: Can I create a index on a column of Type datetime?wangxu12 Apr