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
replication.
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 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
> replication.
> 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 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
>> replication.
>> 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.

> 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