I had two table and joined query took some long time.
I added indexes but still it is slow (about 14 seconds).
Can you tell me what can be modified?
it took about 10 seconds, too late still.

My table
mysql> desc CDS_SERVER_LOG_TBL;
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| RequestTime | varchar(12) | YES | | NULL | |
| DirectionGubun | varchar(2) | YES | | NULL | |
| Min | varchar(12) | YES | MUL | NULL | |
| NewMin | varchar(12) | YES | | NULL | |
| JuminNum | varchar(13) | YES | | NULL | |
| ServiceAdminNum | varchar(10) | YES | | NULL | |
| Mdn | varchar(12) | YES | | NULL | |
| Imsi | varchar(15) | YES | | NULL | |
| KIT | varchar(50) | YES | | NULL | |
| EquipType | varchar(4) | YES | | NULL | |
| OptionalServiceCode | varchar(30) | YES | MUL | NULL | |
| CDS_LOG_DATE | datetime | YES | MUL | NULL | |
+---------------------+-------------+------+-----+---------+-------+
mysql> desc OPTIONAL_SERVICE_CODE_TB
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| CDSBitLocation | varchar(2) | YES | MUL | NULL | |
| OptionalServCode | varchar(10) | NO | | | |
| TIGServCode | varchar(15) | YES | | NULL | |
| ServiceName | varchar(100) | YES | | NULL | |
+------------------+--------------+------+-----+---------+-------+
My index
create index cds_idx1 on CDS_SERVER_LOG_TBL (CDS_LOG_DATE, DirectionGubun) USING BTREE;
create index cds_idx2 on CDS_SERVER_LOG_TBL (OptionalServiceCode) USING BTREE;
create index cds_idx3 on CDS_SERVER_LOG_TBL (MIN) USING BTREE;
create index optional_idx1 on OPTIONAL_SERVICE_CODE_TBL(CDSBitLocation) USING BTREE;

my query plan is
EXPLAIN SELECT
COUNT(MIN)
FROM CDS_SERVER_LOG_TBL AS a, OPTIONAL_SERVICE_CODE_TBL AS b
WHERE a.OptionalServiceCode=b.CDSBitLocation
AND DATE_FORMAT(a.CDS_LOG_DATE,'%Y-%m-%d') BETWEEN DATE_FORMAT('20111109','%Y-%m-%d') AND DATE_FORMAT('20111110','%Y-%m-%d')
AND (a.DirectionGubun = 'A1' OR a.DirectionGubun = 'Z2' OR a.DirectionGubun = 'C1')
;

result it took about 10 seconds
+----+-------------+-------+------+---------------+---------------+---------+----------------------------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+----------------------------------------+---------+--------------------------+
| 1 | SIMPLE | a | ALL | cds_idx2 | NULL | NULL | NULL | 6152116 | Using where |
| 1 | SIMPLE | b | ref | optional_idx1 | optional_idx1 | 7 | customer_service.a.OptionalServiceCode | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+---------------+---------+----------------------------------------+---------+--------------------------+

here's your problem --
this requires a table scan

change it to this --obviously, you may wish to change the "between" values

Also change:
to:
Dave Nance

good call, dave, that's a lot better for readability and simplicity

but, just so that nobody gets the wrong impression (see thread title), this improvement actually does not have any effect on performance

First "AND a.CDS_LOG_DATE BETWEEN ... "modification truly took effect and use index and below 1 seconds result.
Thank you, I didn't recognize that point before, but I am not sure

Seconds modification make change or getting wrong effect.
after change "AND a.DirectionGubun IN ( 'A1','Z2','C1'),"
It took about 0.02 seconds more.

So I like to use query based on modification of CDS_LOG_DATE.
Thank you very much for you help.

Oracle I am accustomed little bit more, but mysql query still difficult to me.
but basis is almost same.