List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 4 2003 11:36pm
Subject:Re: query time in ~3M row table
View as plain text  
In the last episode (Nov 04), jabbott@stripped said:
> 
> I have a question about how long queries should be taking and if my server is too
> small for what I want to be doing.  I have a table setup to record stats from an apache
> web server.  I import the file currently once per month.  Here is my table:
> mysql> describe hitStats;
>
> +-------------+--------------+-------------------+------+-----+---------------------+----------------+
> | Field       | Type         | Collation         | Null | Key | Default             |
> Extra          |
>
> +-------------+--------------+-------------------+------+-----+---------------------+----------------+
> | hostIP      | varchar(24)  | latin1_swedish_ci |      |     |                     |
>                |
> | apacheDate  | datetime     | latin1_swedish_ci |      | MUL | 0000-00-00 00:00:00 |
>                |
> | status      | int(11)      | binary            |      |     | 0                   |
>                |
> | bytes       | varchar(20)  | latin1_swedish_ci |      |     |                     |
>                |
> | contentType | varchar(40)  | latin1_swedish_ci |      |     |                     |
>                |
> | url         | varchar(255) | latin1_swedish_ci |      | MUL |                     |
>                |
> | referer     | text         | latin1_swedish_ci |      |     |                     |
>                |
> | agent       | text         | latin1_swedish_ci |      |     |                     |
>                |
> | statID      | int(11)      | binary            |      | PRI | NULL                |
> auto_increment |
>
> +-------------+--------------+-------------------+------+-----+---------------------+----------------+
> 
> So, about 3 million rows.
> 
> I have read the http://www.mysql.com/doc/en/Server_parameters.html
> page and think that I did what it told me to.
> 
> When I try to do any sort of query the times are really long.  Such as:
> 
> mysql> select count(*) from hitstats where year(apacheDate) = 2003 and
> month(apacheDate) = 9;                               
> +----------+
> | count(*) |
> +----------+
> |   988759 |
> +----------+
> 1 row in set (25.17 sec)

Neither of those constraints can use indexes, so mysql is basically
doing a full index scan of apacheDate (you can verify this by doing an
EXPLAIN SELECT).  Try

select count(*) from hitstats where apacheDate between 20030901000000 and 20030999999999

Mysql stores dates in integer format internally, which is why you can
cheat and use all 9's for an end day and time.

-- 
	Dan Nelson
	dnelson@stripped
Thread
query time in ~3M row tablejabbott4 Nov
  • Re: query time in ~3M row tableBrent Baisley4 Nov
    • Re: query time in ~3M row tablejabbott5 Nov
      • Re: query time in ~3M row tableMatt W5 Nov
        • Re: query time in ~3M row tableBrent Baisley5 Nov
          • Re: query time in ~3M row tableDan Nelson5 Nov
            • Re: query time in ~3M row tableBrent Baisley5 Nov
      • Re: query time in ~3M row tableBrent Baisley5 Nov
      • Re: query time in ~3M row tableMatt W5 Nov
  • Re: query time in ~3M row tableDan Nelson5 Nov