Hi All,
This query takes 3-4 minutes to run from the mysql> command line:
select DAYOFMONTH(started_date), object_status, count(*) from sessobjs
where MONTH(started_date)=3 group by started_date, object_status
Sample Output:
mysql> select DAYOFMONTH(started_date), object_status, count(*) from
sessobjs
where MONTH(started_date)=3 group by started_date, object_status;
+--------------------------+---------------+----------+
| DAYOFMONTH(started_date) | object_status | count(*) |
+--------------------------+---------------+----------+
| 1 | 1 | 185 |
| 1 | 2 | 23647 |
| 1 | 3 | 257 |
| 1 | 4 | 304 |
| 1 | 5 | 69 |
| 1 | 6 | 9 |
| 1 | 7 | 13 |
| 2 | 1 | 403 |
| 2 | 3 | 271 |
| 2 | 4 | 283 |
| 2 | 5 | 32 |
| 2 | 7 | 14 |
| 3 | 1 | 811 |
| 3 | 2 | 20582 |
| 3 | 3 | 243 |
| 3 | 4 | 287 |
| 3 | 5 | 124 |
| 3 | 6 | 1 |
| 3 | 7 | 43 |
However, with the addition of a where clause, this query only a few seconds
to run:
select DAYOFMONTH(started_date), object_status, count(*) from seessobjs
where cell_id=1 AND MONTH(started_date)=3 group by started_date,
object_status
Sample output:
mysql> select DAYOFMONTH(started_date), object_status, count(*) from
sessobjs
-> where cell_id=29 and MONTH(started_date)=3 group by started_date,
object_status;
+--------------------------+---------------+----------+
| DAYOFMONTH(started_date) | object_status | count(*) |
+--------------------------+---------------+----------+
| 1 | 2 | 205 |
| 2 | 2 | 195 |
| 3 | 2 | 177 |
| 4 | 2 | 223 |
| 4 | 3 | 1 |
| 5 | 2 | 194 |
| 6 | 2 | 195 |
| 7 | 2 | 12 |
+--------------------------+---------------+----------+
8 rows in set (2.45 sec)
Here is my index structure for the table SESSOBJS:
mysql> show index from sessobjs;
+----------+------------+---------------+--------------+---------------+----
-------+-------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part |
+----------+------------+---------------+--------------+---------------+----
-------+-------------+----------+
| sessobjs | 0 | PRIMARY | 1 | sobj_id | A
| 3449788 | NULL |
| sessobjs | 1 | sess_id | 1 | sess_id | A
| NULL | NULL |
| sessobjs | 1 | object_status | 1 | object_status | A
| NULL | NULL |
| sessobjs | 1 | objda | 1 | started_date | A
| NULL | NULL |
| sessobjs | 1 | objda | 2 | object_status | A
| NULL | NULL |
| sessobjs | 1 | cell_id | 1 | cell_id | A
| NULL | NULL |
| sessobjs | 1 | cell_id | 2 | started_date | A
| NULL | NULL |
| sessobjs | 1 | cell_id | 3 | object_status | A
| NULL | NULL |
| sessobjs | 1 | site_id | 1 | site_id | A
| NULL | NULL |
| sessobjs | 1 | site_id | 2 | started_date | A
| NULL | NULL |
| sessobjs | 1 | site_id | 3 | object_status | A
| NULL | NULL |
| sessobjs | 1 | host_id | 1 | host_id | A
| NULL | NULL |
| sessobjs | 1 | sess_id_2 | 1 | sess_id | A
| NULL | NULL |
| sessobjs | 1 | sess_id_2 | 2 | started_date | A
| NULL | NULL |
| sessobjs | 1 | sess_id_2 | 3 | object_status | A
| NULL | NULL |
+----------+------------+---------------+--------------+---------------+----
-------+-------------+----------+
And the table structure:
mysql> describe sessobjs;
+---------------+-------------+------+-----+---------------------+----------
------+
| Field | Type | Null | Key | Default | Extra
|
+---------------+-------------+------+-----+---------------------+----------
------+
| sobj_id | int(11) | | PRI | 0 |
auto_increment |
| host_id | int(11) | | MUL | 0 |
|
| object_name | varchar(50) | | | |
|
| object_type | varchar(20) | YES | | NULL |
|
| started | datetime | | | 0000-00-00 00:00:00 |
|
| finished | datetime | YES | | NULL |
|
| object_size | int(15) | YES | | NULL |
|
| backup_mode | varchar(15) | YES | | NULL |
|
| object_status | int(11) | | MUL | 0 |
|
| sess_id | int(11) | | MUL | 0 |
|
| started_date | date | | MUL | 0000-00-00 |
|
| cell_id | int(11) | | MUL | 0 |
|
| site_id | int(11) | | MUL | 0 |
|
+---------------+-------------+------+-----+---------------------+----------
------+
13 rows in set (0.66 sec)
In other queries, I frequently do left joins on sess_id, host_id, cell_id, &
site_id, which is why I've indexed each of those. Can anyone recommend a
better indexing scheme? What I am doing wrong?
Appreciate any help,
Thanks,
-Bill Clements
(Please reply to my e-mail address as well, as I am not subscribed to the
mysql-mailing list.)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
