List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 18 1999 5:56pm
Subject:select count() weirdness
View as plain text  
>>>>> "Kristian" == Kristian Soerensen <elof@stripped> writes:

Kristian> Welcome to the MySQL monitor.  Commands end with ; or \g.
Kristian> Your MySQL connection id is 805 to server version: 3.22.22

Kristian> Type 'help' for help.

mysql> select count(URI) from RESdk_19991017;
Kristian> +------------+
Kristian> | count(URI) |
Kristian> +------------+
Kristian> |      86418 |
Kristian> +------------+
Kristian> 1 row in set (0.01 sec)

mysql> select 60*count(URI)/252 from RESdk_19991017;
Kristian> +-------------------+
Kristian> | 60*count(URI)/252 |
Kristian> +-------------------+
Kristian> |          20579.05 |
Kristian> +-------------------+
Kristian> 1 row in set (13.45 sec)

mysql> explain select count(URI) from RESdk_19991017;
Kristian> +------------------------------+
Kristian> | Comment                      |
Kristian> +------------------------------+
Kristian> | Select tables optimized away |
Kristian> +------------------------------+
Kristian> 1 row in set (0.00 sec)

mysql> explain select 60*count(URI)/252 from RESdk_19991017;
Kristian>
> +----------------+------+---------------+------+---------+------+-------+-------+
Kristian> | table          | type | possible_keys | key  | key_len | ref  | rows  |
Kristian> Extra |
Kristian>
> +----------------+------+---------------+------+---------+------+-------+-------+
Kristian> | RESdk_19991017 | ALL  | NULL          | NULL |    NULL | NULL | 87742 |
Kristian> |
Kristian>
> +----------------+------+---------------+------+---------+------+-------+-------+
Kristian> 1 row in set (0.00 sec)


Kristian> These queries are for a progress monitor for an application where a lot's
Kristian> of perl tasks are doing insert's at 20E3 inserts an hour, but when the
Kristian> second query is run all inserts halts while the query lasts.

Kristian> In this particular case I can use the first query and do the simple math
Kristian> in the application, but it seems rather silly that the select count()
Kristian> optimization isn't used in the second query.

Kristian> Any ideas, or schould I shift to Oracle to get read-while-write ;-)

Hi!

The above is already fixed in the latest MySQL 3.22 version.

Regards,
Monty
Thread
select count() weirdnessKristian Soerensen18 Oct
  • select count() weirdnessMichael Widenius18 Oct