List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 28 2003 5:56pm
Subject:Re: strange behavior with query optimizer regarding 'range'
View as plain text  
In the last episode (Apr 28), jmat@stripped said:
> >Description:
> 
> I have a table that collects hit statistics for a website.  I am storing the data
> hourly, based on a user
> identifier. The table structure:
> 
> CREATE TABLE hits_hourly (
>   uid mediumint(8) unsigned NOT NULL default '0',
>   hournum int(3) unsigned NOT NULL default '0',
>   hitcount smallint(5) unsigned NOT NULL default '0',
>   PRIMARY KEY  (hournum,uid),
>   KEY uid (uid)
> ) TYPE=MyISAM DELAY_KEY_WRITE=1;
> 
> But... when I do more than 3...
> 
> explain select sum(hitcount) from hits_hourly where hournum between 17560744 and
> 17560747;
> +-------------+------+---------------+------+---------+------+-------+-------------+
> | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
> +-------------+------+---------------+------+---------+------+-------+-------------+
> | hits_hourly | ALL  | PRIMARY       | NULL |    NULL | NULL | 21674 | Using where |
> +-------------+------+---------------+------+---------+------+-------+-------------+
> 
> we go back to a full table scan, and it's VERY slow (well, comparitively... 0.40
> seconds to .04 seconds).

How many rows total are in your table?  Mysql might have decided that a
full table scan would be faster than a range scan and multiple random
record pulls (it has to go to the record to get hitcount).
 
> If I create a new index, consisting of (hournum,hitcount), it will
> use that key consistently -- but that is duplicating a lot of data
> into the index that shouldn't really be there.

Remember, all indexes are "duplicating" data :)  With that index,
though, the query will always be very fast since it can use that index
to generate your result without having to do any table lookups at all.

-- 
	Dan Nelson
	dnelson@stripped
Thread
strange behavior with query optimizer regarding 'range'jmat28 Apr
  • Re: strange behavior with query optimizer regarding 'range'Dan Nelson28 Apr
  • Re: strange behavior with query optimizer regarding 'range'Justin Matlock30 Apr