List:General Discussion« Previous MessageNext Message »
From:Justin Swanhart Date:July 14 2004 5:06pm
Subject:Re: do i need an index for this?
View as plain text  
Creating a key will make that query execute very fast,
but if that is the only reason for the key you are
going to be trading quite a lot of space for the speed
of one query.  

How often are you going to run this query?  If you
have 324 million rows, then that index is going to
consume somewhere in the order of 2G or more of disk
space.  Is it worth using all that space to make one
query faster?

--- J S <vervoom@stripped> wrote:
> Hi,
> 
> I want to find the earliest and latest times in the
> time column of my table 
> internet_usage:
> 
>
+--------------+----------------------+------+-----+---------+-------+
> | Field        | Type                 | Null | Key |
> Default | Extra |
>
+--------------+----------------------+------+-----+---------+-------+
> | uid          | int(10) unsigned     |      | MUL |
> 0       |       |
> | time         | timestamp(14)        | YES  |     |
> NULL    |       |
> | ip           | int(10) unsigned     |      |     |
> 0       |       |
> | urlid        | int(10) unsigned     |      |     |
> 0       |       |
> | timetaken    | smallint(5) unsigned | YES  |     |
> 0       |       |
> | cs_size      | int(10) unsigned     | YES  |     |
> 0       |       |
> | sc_size      | int(10) unsigned     | YES  |     |
> 0       |       |
> | method_ID    | tinyint(3) unsigned  |      |     |
> 0       |       |
> | action_ID    | tinyint(3) unsigned  |      |     |
> 0       |       |
> | virus_ID     | tinyint(3) unsigned  |      |     |
> 0       |       |
> | useragent_ID | smallint(5) unsigned |      |     |
> 0       |       |
>
+--------------+----------------------+------+-----+---------+-------+
> 
> 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?
> 
> Thanks for any help.
> 
> JS.
> 
>
_________________________________________________________________
> It's fast, it's easy and it's free. Get MSN
> Messenger today! 
> http://www.msn.co.uk/messenger
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/mysql?unsub=1
> 
> 

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