Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    24

    Unanswered: Trying to speed up result set

    Hi Folks,

    I'm having a hard time speeding up the results from the following query:

    SELECT DISTINCT a.busname, a.bcity, a.bprov, a.row_id, b.province_code
    FROM ccontrib a, crosswalk b, category c
    WHERE a.bprov = b.province AND a.privflag NOT LIKE 'R' AND c.contcode = a.contcode AND c.catcode LIKE '1MBR1'
    ORDER BY busname

    It takes about 86 seconds to retrieve a total of 7622 records, from 3 tables. Can anyone think of a way to reduce this time? When I only had 2 tables, the results were almost instant (3s for 14000+ records).

    Thanks!

  2. #2
    Join Date
    Jul 2002
    Posts
    9
    Could be attacked from more than one angle I am sure.

    'Not like' are killers.
    Check your indexes to make sure the columns you are joining on are indexed.
    Also is the order by necessary.

  3. #3
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Trying to speed up result set

    Originally posted by DataWho
    Hi Folks,

    I'm having a hard time speeding up the results from the following query:

    SELECT DISTINCT a.busname, a.bcity, a.bprov, a.row_id, b.province_code
    FROM ccontrib a, crosswalk b, category c
    WHERE a.bprov = b.province AND a.privflag NOT LIKE 'R' AND c.contcode = a.contcode AND c.catcode LIKE '1MBR1'
    ORDER BY busname

    It takes about 86 seconds to retrieve a total of 7622 records, from 3 tables. Can anyone think of a way to reduce this time? When I only had 2 tables, the results were almost instant (3s for 14000+ records).

    Thanks!
    Can you provide the following:

    1-Description of every table involved in the query (use DESCRIBE tablename)
    2-Available indexes of those tables (SHOW INDEX FROM tablename)
    3-An EXPLAIN of your query (EXPLAIN SELECT DISTINCT a.busname, a.bcity, a.bprov, a.row_id, b.province_code
    FROM ccontrib a, crosswalk b, category c
    WHERE a.bprov = b.province AND a.privflag NOT LIKE 'R' AND c.contcode = a.contcode AND c.catcode LIKE '1MBR1' ORDER BY busname)

    Once we have that, it'll take 5 seconds to find out why your query is so slow.

    Most likely, there's a join between one of your tables (the new table) that is missing. Adding an index would most likely solve the problem... But as I said, until we have more info not much can be solved...

    Hope this helps

  4. #4
    Join Date
    Oct 2002
    Posts
    24

    Re: Trying to speed up result set

    +------------+--------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    Here is the DESCRIBE ccontrib:

    +------------+--------------+------+-----+---------+----------------+
    | row_id | int(11) | | PRI | NULL | auto_increment |
    | salutation | varchar(10) | YES | | NULL | |
    | lname | varchar(25) | YES | | NULL | |
    | fname | varchar(17) | YES | | NULL | |
    | title | varchar(46) | YES | | NULL | |
    | busname | varchar(65) | YES | | NULL | |
    | baddress | varchar(32) | YES | | NULL | |
    | bcity | varchar(20) | YES | | NULL | |
    | bprov | varchar(22) | YES | MUL | NULL | |
    | bpostal | varchar(10) | YES | | NULL | |
    | bphone | varchar(20) | YES | | NULL | |
    | haddress | varchar(32) | YES | | NULL | |
    | contcode | varchar(7) | YES | MUL | NULL | |
    | privflag | char(1) | YES | | NULL | |
    | email1 | varchar(60) | YES | | NULL | |
    | bfax | varchar(100) | | | | |
    | www | varchar(60) | YES | | NULL | |
    +------------+--------------+------+-----+---------+----------------+

    Here's the DESCRIBE category:
    +----------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | catcode | varchar(14) | YES | | NULL | |
    | contcode | varchar(7) | YES | MUL | NULL | |
    +----------+-------------+------+-----+---------+-------+

    Here's DESCRIBE crosswalk:
    +----------------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------+--------------+------+-----+---------+-------+
    | province_code | varchar(100) | | | | |
    | province_label | varchar(100) | | | | |
    | province | varchar(100) | | MUL | | |
    +----------------+--------------+------+-----+---------+-------+

    SHOW INDEX from ccontrib:
    +----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | ccontrib | 0 | PRIMARY | 1 | row_id | A | 17536 | NULL | NULL | | BTREE | |
    | ccontrib | 1 | contcodes | 1 | contcode | A | 2505 | NULL | NULL | YES | BTREE | |
    | ccontrib | 1 | provinces | 1 | bprov | A | 143 | NULL | NULL | YES | BTREE | |
    +----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

    SHOW INDEX FROM category:
    +----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | category | 1 | contcodes | 1 | contcode | A | 11990 | NULL | NULL | YES | BTREE | |
    +----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

    SHOW INDEX FROM crosswalk:
    +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | crosswalk | 1 | provinces | 1 | province | A | NULL | NULL | NULL | | BTREE | |
    +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

    And finally, the EXPLAIN

    mysql> EXPLAIN SELECT DISTINCT a.fname, a.lname, a.busname, a.bcity, a.bprov, a.row_id, b.province_code FROM ccontrib a, crosswalk b, category c WHERE a.bprov = b.province AND c.contcode = a.contcode AND c.catcode = '1MBR1'
    -> ;
    +-------+------+---------------------+-----------+---------+------------+------+-----------------------+
    | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +-------+------+---------------------+-----------+---------+------------+------+-----------------------+
    | b | ALL | provinces | NULL | NULL | NULL | 41 | Using temporary |
    | a | ref | contcodes,provinces | provinces | 23 | b.province | 122 | Using where |
    | c | ref | contcodes | contcodes | 8 | a.contcode | 10 | Using where; Distinct |
    +-------+------+---------------------+-----------+---------+------------+------+-----------------------+

    After indexing and chopping up the query a little bit, I've got it down to between 6s and 9s for the query, but it's still a long time to wait in between clicking pages when browsing the recordset online.

    Hope you can help! Thanks!

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Trying to speed up result set

    Originally posted by DataWho
    +------------+--------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    Here is the DESCRIBE ccontrib:

    +------------+--------------+------+-----+---------+----------------+
    | row_id | int(11) | | PRI | NULL | auto_increment |
    | salutation | varchar(10) | YES | | NULL | |
    | lname | varchar(25) | YES | | NULL | |
    | fname | varchar(17) | YES | | NULL | |
    | title | varchar(46) | YES | | NULL | |
    | busname | varchar(65) | YES | | NULL | |
    | baddress | varchar(32) | YES | | NULL | |
    | bcity | varchar(20) | YES | | NULL | |
    | bprov | varchar(22) | YES | MUL | NULL | |
    | bpostal | varchar(10) | YES | | NULL | |
    | bphone | varchar(20) | YES | | NULL | |
    | haddress | varchar(32) | YES | | NULL | |
    | contcode | varchar(7) | YES | MUL | NULL | |
    | privflag | char(1) | YES | | NULL | |
    | email1 | varchar(60) | YES | | NULL | |
    | bfax | varchar(100) | | | | |
    | www | varchar(60) | YES | | NULL | |
    +------------+--------------+------+-----+---------+----------------+

    Here's the DESCRIBE category:
    +----------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | catcode | varchar(14) | YES | | NULL | |
    | contcode | varchar(7) | YES | MUL | NULL | |
    +----------+-------------+------+-----+---------+-------+

    Here's DESCRIBE crosswalk:
    +----------------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------+--------------+------+-----+---------+-------+
    | province_code | varchar(100) | | | | |
    | province_label | varchar(100) | | | | |
    | province | varchar(100) | | MUL | | |
    +----------------+--------------+------+-----+---------+-------+

    ...

    After indexing and chopping up the query a little bit, I've got it down to between 6s and 9s for the query, but it's still a long time to wait in between clicking pages when browsing the recordset online.

    Hope you can help! Thanks!

    The first think that I noticed is that ccontrib.bprov and crosswalk.province are used in one of your joins (and both indexed) BUT they are not of the same length : one is a VARCHAR(22) and the other is a VARCHAR(100).

    My guess is that by fixing this you should get the performance you expect.

    Let me know if that works! We'll check other things if that doesn't work.

    Hope this helps

Posting Permissions

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