List:General Discussion« Previous MessageNext Message »
From:John Larsen Date:July 14 2004 7:13pm
Subject:Re: do i need an index for this?
View as plain text  
Hmmw your syntax should ork. Possibly try
CREATE INDEX urlindex ON internet_usage (time(8));
W 8hich will only use the first charaters of your time field, it should 
make the index significantly smaller in size.
Though I'm not completly sure shorting time field this way is legal, but 
you can always try.
J S wrote:

> Thanks for your reply. Can I just check my syntax is right for 
> creating the key?
>
> CREATE INDEX urlindex ON internet_usage (time);
>
> I ran this before but got an error 27 (eventhough the filesize ulimit 
> was set to unlimited). So just wondered if I was using the wrong syntax?
>
> JS.
>
>>
>> In the last episode (Jul 14), J S said:
>> > I want to find the earliest and latest times in the time column of my
>> > table internet_usage:
>> >
>> > +--------------+----------------------+------+-----+---------+-------+
>> > | Field        | Type                 | Null | Key | Default | Extra |
>> > +--------------+----------------------+------+-----+---------+-------+
>> > | time         | timestamp(14)        | YES  |     | NULL    |       |
>> > +--------------+----------------------+------+-----+---------+-------+
>> >
>> > So far there are 324936160 rows. If I do :
>> >
>> > SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage;
>> >
>> > I can see the query is going to run for a long time. Do I have to
>> > create an index on time to speed this up or is there another way of
>> > doing it?
>>
>> Most definitely.  An index will make that query almost instantaneous.
>> Mysql won't even have to hit the table at all, and just has to look at
>> the first and last index blocks.
>>
>> -- 
>>     Dan Nelson
>>     dnelson@stripped
>>
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    
>> http://lists.mysql.com/mysql?unsub=1
>>
>
> _________________________________________________________________
> Stay in touch with absent friends - get MSN Messenger 
> http://www.msn.co.uk/messenger
>
>

Thread
do i need an index for this?J S14 Jul
  • Re: do i need an index for this?Dan Nelson14 Jul
  • Re: do i need an index for this?Justin Swanhart14 Jul
Re: do i need an index for this?J S14 Jul
  • Re: do i need an index for this?Dan Nelson14 Jul
  • Re: do i need an index for this?John Larsen14 Jul
    • Re: do i need an index for this?Dan Nelson14 Jul
Re: do i need an index for this?J S14 Jul