List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 7 1999 10:42am
Subject:Re: Help: Indexes and Count(*)
View as plain text  
Brandon Shuey wrote:
> 
> I have a table with several(7+million) million rows of data.  I want to group count
> as portion of the data.  However, mysql is
> performing a complete table scan and IT TAKES FOREVER (several hours).
> How can I speed this thing up?  I was hoping that indexes would have helped!  Here is
> the table and the query and the explain
> result:
> 
> The total data between '1999-05-01 00:00:00' and '1999-05-02 00:00:00' is 80488
> (24.7% of all the records).
> Shouldn't it use an index for this?
> 
> describe tblVisits;
> +-----------------+-----------+------+-----+---------------------+-------+
> | Field           | Type      | Null | Key | Default             | Extra |
> +-----------------+-----------+------+-----+---------------------+-------+
> | VisitLogID      | char(20)  |      | PRI |                     |       |
> | HostAccount     | char(20)  |      | MUL |                     |       |
> | Style           | char(20)  |      |     |                     |       |
> | Category        | char(20)  |      |     |                     |       |
> | Server          | char(20)  |      |     |                     |       |
> | UserID          | char(20)  |      |     |                     |       |
> | Format          | char(20)  |      |     |                     |       |
> | Source          | char(20)  |      |     |                     |       |
> | RemoteIP        | char(20)  |      | MUL |                     |       |
> | UserAgent       | char(20)  |      |     |                     |       |
> | RemoteHost      | char(20)  |      |     |                     |       |
> | Referer         | char(100) |      |     |                     |       |
> | Date_Time       | datetime  |      | MUL | 0000-00-00 00:00:00 |       |
> | ReferralAccount | char(20)  |      |     |                     |       |
> | Summarized      | int(11)   |      | MUL | 0                   |       |
> | Quality         | int(11)   |      |     | 0                   |       |
> +-----------------+-----------+------+-----+---------------------+-------+
> show index from  tblVisits;
>
> +-----------+------------+------------------+--------------+-------------+-----------+-------------+----------+
> | Table     | Non_unique | Key_name         | Seq_in_index | Column_name | Collation
> | Cardinality | Sub_part |
>
> +-----------+------------+------------------+--------------+-------------+-----------+-------------+----------+
> | tblVisits |          0 | PRIMARY          |            1 | VisitLogID  | A        
> |      332995 |     NULL |
> | tblVisits |          1 | HostAccount_indx |            1 | HostAccount | A        
> |        NULL |     NULL |
> | tblVisits |          1 | HostAccount_indx |            2 | Style       | A        
> |        NULL |     NULL |
> | tblVisits |          1 | HostAccount_indx |            3 | Category    | A        
> |        NULL |     NULL |
> | tblVisits |          1 | HostAccount_indx |            4 | Server      | A        
> |        NULL |     NULL |
> | tblVisits |          1 | Date_Time        |            1 | Date_Time   | A        
> |        NULL |     NULL |
> | tblVisits |          1 | Date_Time        |            2 | HostAccount | A        
> |        NULL |     NULL |
> | tblVisits |          1 | Date_Time        |            3 | Style       | A        
> |        NULL |     NULL |
> | tblVisits |          1 | Date_Time        |            4 | Style       | A        
> |        NULL |     NULL |
> | tblVisits |          1 | Date_Time        |            5 | Category    | A        
> |        NULL |     NULL |
> | tblVisits |          1 | Date_Time_2      |            1 | Date_Time   | A        
> |        NULL |     NULL |
> | tblVisits |          1 | Date_Time_2      |            2 | Server      | A        
> |        NULL |     NULL |
> | tblVisits |          1 | RemoteIP         |            1 | RemoteIP    | A        
> |        NULL |     NULL |
> | tblVisits |          1 | RemoteIP         |            2 | Date_Time   | A        
> |        NULL |     NULL |
> | tblVisits |          1 | Summarized       |            1 | Summarized  | A        
> |        NULL |     NULL |
> | tblVisits |          1 | Summarized       |            2 | Quality     | A        
> |        NULL |     NULL |
>
> +-----------+------------+------------------+--------------+-------------+-----------+-------------+----------+
> EXPLAIN SELECT
>         Date_Format(Date_Time,'%Y-%m-%d') as cDate,
>         HostAccount,
>         Style,
>         Category,
>         Count(*)
> FROM tblVisits
> WHERE Date_Time >= '1999-05-01 00:00:00' and Date_Time <'1999-05-02 00:00:00'
> GROUP BY cDate,HostAccount,Style,Category;
>
> +-----------+------+-----------------------+------+---------+------+--------+------------+
> | table     | type | possible_keys         | key  | key_len | ref  | rows   | Extra  
>    |
>
> +-----------+------+-----------------------+------+---------+------+--------+------------+
> | tblVisits | ALL  | Date_Time,Date_Time_2 | NULL |    NULL | NULL | 332995 | where
> used |
>
> +-----------+------+-----------------------+------+---------+------+--------+------------+
> select count(*) from tblVisits;
> +----------+
> | count(*) |
> +----------+
> |   332995 |
> +----------+
> 
> Brandon Shuey

Hi Brandon

Use the BETWEEN syntax to get date ranges:
SELECT
        Date_Format(Date_Time,'%Y-%m-%d') as cDate,
        HostAccount,
        Style,
        Category,
        Count(*)
FROM 
	tblVisits
WHERE 
	Date_Time BETWEEN '1999-05-01 00:00:00' AND '1999-05-02 00:00:00'
GROUP BY cDate,HostAccount,Style,Category;

This will only speed up a little.
The main problem with your query is the GROUP BY part.
Mysql has to calculate each cDate before grouping.

Tschau
Christian

Thread
Help: Indexes and Count(*)(Brandon Shuey)7 May
  • Re: Help: Indexes and Count(*)Dan Nelson7 May
  • Re: Help: Indexes and Count(*)Christian Mack7 May