Professional Web Applications Themes

Simple query, very low performance - MySQL

Newbie: I have only 60 000 rows within a tabe ("result"), but the following query takes very long: mysql> SELECT MIN(received_time) as minimum FROM result; +---------+ | minimum | +---------+ | 0 | +---------+ 1 row in set (16.35 sec) mysql> EXPLAIN SELECT MIN(received_time) as minimum FROM result; +----+-------------+--------+------+---------------+------+---------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+ | 1 | SIMPLE | result | ALL | NULL | NULL | NULL | NULL | 62895 | | +----+-------------+--------+------+---------------+------+---------+ Here are the settings of my.conf: # * ...

  1. #1

    Default Simple query, very low performance


    Newbie: I have only 60 000 rows within a tabe ("result"), but the
    following query takes very long:

    mysql> SELECT MIN(received_time) as minimum FROM result;
    +---------+
    | minimum |
    +---------+
    | 0 |
    +---------+
    1 row in set (16.35 sec)


    mysql> EXPLAIN SELECT MIN(received_time) as minimum FROM result;
    +----+-------------+--------+------+---------------+------+---------+
    | id | select_type | table | type | possible_keys | key | key_len |
    ref | rows | Extra |
    +----+-------------+--------+------+---------------+------+---------+
    | 1 | SIMPLE | result | ALL | NULL | NULL | NULL |
    NULL | 62895 | |
    +----+-------------+--------+------+---------------+------+---------+

    Here are the settings of my.conf:


    # * Fine Tuning
    key_buffer = 750M
    max_allowed_packet = 2M
    thread_stack = 128K
    query_cache_limit = 2097152
    query_cache_size = 16777216
    query_cache_type = 1
    tion.
    log-bin = /var/log/mysql/mysql-bin.log
    expire-logs-days = 20
    max_binlog_size = 104857600
    discouraged
    skip-bdb


    What should I check, where can I start to optimize?

    best regards
    Bernhard
    Bernhard Kornberger Guest

  2. #2

    Default Re: Simple query, very low performance

    Bernhard Kornberger wrote:
    > Newbie: I have only 60 000 rows within a tabe ("result"), but the
    > following query takes very long:
    >
    > mysql> SELECT MIN(received_time) as minimum FROM result;
    > +---------+
    >> minimum |
    > +---------+
    >> 0 |
    > +---------+
    > 1 row in set (16.35 sec)
    >
    >
    > mysql> EXPLAIN SELECT MIN(received_time) as minimum FROM result;
    > +----+-------------+--------+------+---------------+------+---------+
    >> id | select_type | table | type | possible_keys | key | key_len |
    > ref | rows | Extra |
    > +----+-------------+--------+------+---------------+------+---------+
    >> 1 | SIMPLE | result | ALL | NULL | NULL | NULL |
    > NULL | 62895 | |
    > +----+-------------+--------+------+---------------+------+---------+
    >
    > Here are the settings of my.conf:
    >
    >
    > # * Fine Tuning
    > key_buffer = 750M
    > max_allowed_packet = 2M
    > thread_stack = 128K
    > query_cache_limit = 2097152
    > query_cache_size = 16777216
    > query_cache_type = 1
    > tion.
    > log-bin = /var/log/mysql/mysql-bin.log
    > expire-logs-days = 20
    > max_binlog_size = 104857600
    > discouraged
    > skip-bdb
    >
    >
    > What should I check, where can I start to optimize?
    >
    > best regards
    > Bernhard
    I would suggest an index on the received_time column. That should save it
    from having to examine all the values to see which is the smallest, as long
    as mysql can figure out that for the MIN function it can use the index. I'm
    sure it can, but if not you could change the query to:
    SELECT receive_time as minimum FROM result ORDER BY receive_time LIMIT 1


    Paul Lautman Guest

  3. #3

    Default Re: Simple query, very low performance

    Paul Lautman wrote:
    > Bernhard Kornberger wrote:
    >> Newbie: I have only 60 000 rows within a tabe ("result"), but the
    >> following query takes very long:
    >>
    >> mysql> SELECT MIN(received_time) as minimum FROM result;
    >> +---------+
    >>> minimum |
    >> +---------+
    >>> 0 |
    >> +---------+
    >> 1 row in set (16.35 sec)
    >>
    >>
    >> mysql> EXPLAIN SELECT MIN(received_time) as minimum FROM result;
    >> +----+-------------+--------+------+---------------+------+---------+
    >>> id | select_type | table | type | possible_keys | key | key_len |
    >> ref | rows | Extra |
    >> +----+-------------+--------+------+---------------+------+---------+
    >>> 1 | SIMPLE | result | ALL | NULL | NULL | NULL |
    >> NULL | 62895 | |
    >> +----+-------------+--------+------+---------------+------+---------+
    >>
    >> Here are the settings of my.conf:
    >>
    >>
    >> # * Fine Tuning
    >> key_buffer = 750M
    >> max_allowed_packet = 2M
    >> thread_stack = 128K
    >> query_cache_limit = 2097152
    >> query_cache_size = 16777216
    >> query_cache_type = 1
    >> tion.
    >> log-bin = /var/log/mysql/mysql-bin.log
    >> expire-logs-days = 20
    >> max_binlog_size = 104857600
    >> discouraged
    >> skip-bdb
    >>
    >>
    >> What should I check, where can I start to optimize?
    >>
    >> best regards
    >> Bernhard
    >
    > I would suggest an index on the received_time column. That should
    > save it from having to examine all the values to see which is the
    > smallest, as long as mysql can figure out that for the MIN function
    > it can use the index. I'm sure it can, but if not you could change
    > the query to: SELECT received_time as minimum FROM result ORDER BY
    > received_time
    > LIMIT 1
    OK I just checked it and building the index and changing the query to
    SELECT received_time as minimum FROM result ORDER BY received_time LIMIT 1
    definitely seems the way to go.


    Paul Lautman Guest

  4. #4

    Default Re: Simple query, very low performance

    > OK I just checked it and building the index and changing the query to
    > SELECT received_time as minimum FROM result ORDER BY received_time LIMIT 1
    > definitely seems the way to go.
    >
    >
    Thank you, works perfectly!

    best regards
    Bernhard
    Bernhard Kornberger Guest

Similar Threads

  1. Query Performance Question
    By arisser@gmail.com in forum MySQL
    Replies: 4
    Last Post: April 29th, 05:29 PM
  2. simple (?) query--help
    By Bosconian in forum MySQL
    Replies: 2
    Last Post: December 20th, 12:43 AM
  3. [PHP] Simple forms query
    By Matt Matijevich in forum PHP Development
    Replies: 2
    Last Post: July 9th, 04:50 PM
  4. simple query....
    By rodger in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 2nd, 01:17 PM
  5. query performance mystery
    By Lou Fiorino in forum Oracle Server
    Replies: 1
    Last Post: January 11th, 11:29 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139