In the last episode (Nov 04), [EMAIL PROTECTED] 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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
