List:General Discussion« Previous MessageNext Message »
From:Brandon Shuey Date:May 7 1999 1:55am
Subject:Help: Indexes and Count(*)
View as plain text  
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
WebCentric Inc.
(316) 612-8030
brandon@stripped


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