Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Unanswered: Can this query can be faster?

    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 |
    +----+-------------+-------+------+---------------+---------------+---------+----------------------------------------+---------+--------------------------+

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by s197oo302 View Post
    Can you tell me what can be modified?
    here's your problem --
    Code:
    AND DATE_FORMAT(a.CDS_LOG_DATE,'%Y-%m-%d') BETWEEN ...
    this requires a table scan

    change it to this --
    Code:
    AND a.CDS_LOG_DATE BETWEEN ...
    obviously, you may wish to change the "between" values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also change:
    Code:
    AND (a.DirectionGubun = 'A1' OR a.DirectionGubun = 'Z2' OR a.DirectionGubun = 'C1')
    to:
    Code:
    AND a.DirectionGubun IN ( 'A1','Z2','C1')
    Dave Nance

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    5

    first modification and seconds modification

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •