Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2004
    Posts
    16

    Unanswered: Poor performance for count

    Hi all,

    I'm using MySQL 3.23.54, running on a Sun system 1GHZ with 1MB RAM. My table is about 600,000 records (each is about 50 bytes). When I run the following query it takes 5 secs (!) and returns 226,000:

    SELECT count(*),tran_type from logrecord WHERE device_id=4000 and tran_datetime between '2003-12-23 16:36:09' and '2004-03-23 14:31:28' GROUP BY tran_type

    I've already tries using count(indexed_col) but it didn't help. Also, if it using an index or not doesn't change the time.

    Any idea?

    Thanks,

    Tal

  2. #2
    Join Date
    Mar 2004
    Posts
    8
    make indexes for columns:
    device_id, tran_datetime and tran_type

    These are search or sort columns...

  3. #3
    Join Date
    Mar 2004
    Posts
    16
    I have the following index:

    CREATE INDEX type_rep on logrecord(device_id, tran_type, onus, tran_datetime)

    shouldn't this work? on the explain I see it gets this index and uses it, but it takes the same time with or without it....

  4. #4
    Join Date
    Mar 2004
    Posts
    8
    multiple column index could not work for single column from this index.
    Create indexes for each column. It may help.

  5. #5
    Join Date
    Mar 2004
    Posts
    16
    That didn't do much... it takes the same time...

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

    Re: Poor performance for count

    Originally posted by localt
    Hi all,

    I'm using MySQL 3.23.54, running on a Sun system 1GHZ with 1MB RAM. My table is about 600,000 records (each is about 50 bytes). When I run the following query it takes 5 secs (!) and returns 226,000:

    SELECT count(*),tran_type from logrecord WHERE device_id=4000 and tran_datetime between '2003-12-23 16:36:09' and '2004-03-23 14:31:28' GROUP BY tran_type

    I've already tries using count(indexed_col) but it didn't help. Also, if it using an index or not doesn't change the time.

    Any idea?
    Tal
    If you are using InnoDB table type, this is a well known problem that can't be fixed for now...

  7. #7
    Join Date
    Mar 2004
    Posts
    16

    Re: Poor performance for count

    How can I know it is? Also, if so, how can I change it?

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

    Re: Poor performance for count

    Originally posted by localt
    How can I know it is? Also, if so, how can I change it?
    Well, if you need transations, you have to stay with InnoDb. But if you can live without transactions , then:

    You can tell the type of your tables by looking at: SHOW TABLE STATUS;
    You can change from InnoDb to MyISAM by executing: ALTER TABLE yourtablename TYPE=MyISAM;

    Hope this helps

  9. #9
    Join Date
    Mar 2004
    Posts
    16

    Re: Poor performance for count

    Thanks bstjean, but it didn't.... My tables are already MyISAM.... any more ideas anyone? please?

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

    Re: Poor performance for count

    Originally posted by localt
    Thanks bstjean, but it didn't.... My tables are already MyISAM.... any more ideas anyone? please?
    Do you have a primary key on this table?

    Then try with count(*) and count(yourprimarykey) to test which one is the fastest

  11. #11
    Join Date
    Mar 2004
    Posts
    16

    Re: Poor performance for count

    I have... Same time... I'm really clue less... and it is using the index (or so it says on the explain)...

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

    Re: Poor performance for count

    Originally posted by localt
    I have... Same time... I'm really clue less... and it is using the index (or so it says on the explain)...
    Perhaps you show post a DESCRIBE of your table as well as a SHOW INDEX and the EXPLAIN of your query so we can understand better what is going on...

  13. #13
    Join Date
    Mar 2004
    Posts
    16

    Re: Poor performance for count

    Ok, here it is:

    The discribe of the table:

    mysql> describe logrecord;
    +---------------+---------------+------+-----+---------------------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------------+---------------+------+-----+---------------------+-------+
    | device_id | decimal(7,0) | | | 0 | |
    | device_type | decimal(2,0) | | | 0 | |
    | inst | varchar(15) | | | | |
    | onus | decimal(1,0) | | | 0 | |
    | tran_type | varchar(4) | YES | | NULL | |
    | tran_time | decimal(2,0) | | | 0 | |
    | tp_fee | decimal(4,0) | | | 0 | |
    | chp_fee | decimal(4,0) | | | 0 | |
    | tran_datetime | datetime | | | 0000-00-00 00:00:00 | |
    | amount_auth | decimal(15,0) | YES | | NULL | |
    | void_code | decimal(4,0) | | | 0 | |
    +---------------+---------------+------+-----+---------------------+-------+

    indexes:

    mysql> show index from logrecord;
    +-----------+------------+----------+--------------+---------------+-----------+
    -------------+----------+--------+---------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
    Cardinality | Sub_part | Packed | Comment |
    +-----------+------------+----------+--------------+---------------+-----------+
    -------------+----------+--------+---------+
    | logrecord | 1 | type_rep | 1 | device_id | A |
    55 | NULL | NULL | |
    | logrecord | 1 | type_rep | 2 | tran_type | A |
    344 | NULL | NULL | |
    | logrecord | 1 | type_rep | 3 | onus | A |
    487 | NULL | NULL | |
    | logrecord | 1 | type_rep | 4 | tran_datetime | A |
    576585 | NULL | NULL | |
    | logrecord | 1 | onus_rep | 1 | onus | A |
    1 | NULL | NULL | |
    | logrecord | 1 | onus_rep | 2 | tran_type | A |
    15 | NULL | NULL | |
    | logrecord | 1 | onus_rep | 3 | void_code | A |
    224 | NULL | NULL | |
    | logrecord | 1 | onus_rep | 4 | inst | A |
    655 | NULL | NULL | |
    | logrecord | 1 | onus_rep | 5 | tran_datetime | A |
    576585 | NULL | NULL | |
    | logrecord | 1 | onus_rep | 6 | device_type | A |
    576585 | NULL | NULL | |
    | logrecord | 1 | avrg_rep | 1 | device_id | A |
    55 | NULL | NULL | |
    | logrecord | 1 | avrg_rep | 2 | void_code | A |
    1337 | NULL | NULL | |
    | logrecord | 1 | avrg_rep | 3 | tran_type | A |
    2200 | NULL | NULL | |
    | logrecord | 1 | avrg_rep | 4 | amount_auth | A |
    28829 | NULL | NULL | |
    | logrecord | 1 | avrg_rep | 5 | tran_datetime | A |
    576585 | NULL | NULL | |
    | logrecord | 1 | act_rep | 1 | inst | A |
    15 | NULL | NULL | |
    | logrecord | 1 | act_rep | 2 | tran_datetime | A |
    576585 | NULL | NULL | |
    | logrecord | 1 | act_rep | 3 | device_type | A |
    576585 | NULL | NULL | |
    | logrecord | 1 | resp_rep | 1 | tran_time | A |
    10 | NULL | NULL | |
    | logrecord | 1 | resp_rep | 2 | inst | A |
    95 | NULL | NULL | |
    | logrecord | 1 | resp_rep | 3 | tran_datetime | A |
    576585 | NULL | NULL | |
    | logrecord | 1 | resp_rep | 4 | device_type | A |
    576585 | NULL | NULL | |
    +-----------+------------+----------+--------------+---------------+-----------+
    -------------+----------+--------+---------+

    and the explain:

    mysql> explain SELECT count(*),tran_type from logrecord WHERE device_id=4000 and
    tran_datetime between '2003-12-23 16:36:09' and '2004-03-23 14:31:28' GROUP BY
    tran_type;
    +-----------+------+-------------------+----------+---------+-------+--------+--
    -----------------------+
    | table | type | possible_keys | key | key_len | ref | rows | E
    xtra |
    +-----------+------+-------------------+----------+---------+-------+--------+--
    -----------------------+
    | logrecord | ref | type_rep,avrg_rep | type_rep | 8 | const | 260164 | w
    here used; Using index |
    +-----------+------+-------------------+----------+---------+-------+--------+--
    -----------------------+

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the jolly factor, could you try:
    PHP Code:
    CREATE INDEX ptp_junque ON logrecord (device_idtran_datetimetran_type
    If that doesn't help, then try using myisamchk to force that index order on the data rows themselves.

    -PatP

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...That's where you're hanging out, traitor!

Posting Permissions

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