Hi,
Without a where clause in your initial query, you will never get to use an
index.
How many rows are there in sessobjs? From the start of the sample output, it
looks like there are several thousand, so asking a complex question may,
indeed, take a long time.
You could create a result table, and use several cell_id limited queries to
populated it, if the time take to run the query for each cell_id turns out
to be less than the time to run the one-off query.
Hope this helps.
Quentin
-----Original Message-----
From: CLEMENTS,BILL (HP-Roseville,ex1) [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 8 March 2001 1:27 p.m.
To: '[EMAIL PROTECTED]'
Subject: Query Optimization
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
The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended
recipient, you are asked to respect that confidentiality and not
disclose, copy or make use of its contents. If received in error
you are asked to destroy this email and contact the sender immediately.
Your assistance is appreciated.
---------------------------------------------------------------------
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
