Query slows down, when repeated

Hi,
i’m experiencing so strange things on my new installed mysql-server-machine: its an opteron machine with 4GB ram and 2 SATA harddrives. Its running an up-to-date 64bit debian etch installation with software raid1.

Now heres the problem:
I have one table:
CREATE TABLE test2 (
id int(11) NOT NULL auto_increment,
who int(11) NOT NULL,
whom int(11) NOT NULL,
timestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
hide tinyint(4) NOT NULL default ‘0’,
PRIMARY KEY (id),
KEY who (who,timestamp)
) ENGINE=MyISAM AUTO_INCREMENT=12291 DEFAULT CHARSET=utf8

When execution this query something very strange (for me) happends:
SELECT * FROM test2 WHERE whom=14 AND timestamp>‘2007-02-26 03:00’;

When executed for the first time:
67 rows in set (0.50 sec)
Okay, everything fine!

Now the same query repeated, nothing else is done on the server:
67 rows in set (0.79 sec)
67 rows in set (1.34 sec)
67 rows in set (2.25 sec)
67 rows in set (3.78 sec)
67 rows in set (6.35 sec)
67 rows in set (10.67 sec)
67 rows in set (17.91 sec)
67 rows in set (30.06 sec)
67 rows in set (50.49 sec)
67 rows in set (1 min 24.76 sec)
67 rows in set (2 min 0.01 sec)

SELECT * FROM test2 WHERE whom=14; and SELECT * FROM test2 WHERE timestamp>‘2007-02-26 03:00’;
work fine, no slowing down.

Turning on and off the query cache doesn’t change anything, but there seems to be no slowing down, when i connect to the server over socket instead of the network connection. It also works when i disconnect and reconnect after each query-execution.

Serversettings are:
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 16M
myisam_sort_buffer_size = 64M
max_connections = 1000
read_buffer_size = 4M
read_rnd_buffer_size = 16M
thread_cache = 64
query_cache_size = 32M
tmp_table_size = 128M
thread_stack = 128K

wait_timeout = 120

query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1

I have no clue whats wrong here. Any hints?

Thx
Claus

Use EXPLAIN SELECT [your query here].
That will give you a hint about what mysql is doing to solve your query.

Why do you not have an index on (whom, timestamp) since that is what you are serching on?
Add it and you shall see that your query is speeding up.

Is there any risk that you are running out of memory on that server?

Are you freeing the result set after each query?

Please don’t use ‘timestamp’ as the column name.
Although you can use backticks to quote them, Reserved words are reserved for a reason.

[B]sterin wrote on Mon, 19 March 2007 14:21[/B]
1. Use EXPLAIN SELECT [your query here]. That will give you a hint about what mysql is doing to solve your query.

±—±------------±-------±-----±--------------±-----± --------±-----±------±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-------±-----±--------------±-----± --------±-----±------±------------+
| 1 | SIMPLE | test2 | ALL | NULL | NULL | NULL | NULL | 10822 | Using where |
±—±------------±-------±-----±--------------±-----± --------±-----±------±------------+

I can’t see anything unusal here (except that there is no key, but i don’t think thats the point)

[B]sterin wrote on Mon, 19 March 2007 14:21[/B]
2. Why do you not have an index on (whom, timestamp) since that is what you are serching on? Add it and you shall see that your query is speeding up.

There is no key yet, but it doesn’t make a difference: The same query using “who” instead auf “whom” in the where-clause has the same problem: it keeps slowing down.

[B]sterin wrote on Mon, 19 March 2007 14:21[/B]
3. Is there any risk that you are running out of memory on that server?

No way. Its a complete fresh installation, there is only the mysql-server runing and no traffic except of my test-queries. Nothing that could use 4GB Ram.
The query also works fine on the old server, which has only 1GB RAM and the webserver running, so i suppose its neither the query itself nor the table structure which is responsible for that.

[B]sterin wrote on Mon, 19 March 2007 14:21[/B]
4. Are you freeing the result set after each query?

I’m just executing the query in the command-line interface, i don’t think there is a need to free the result (how would i do that in the command-line interface?)

[B]sterin wrote on Mon, 19 March 2007 14:21[/B]
5. Please don't use 'timestamp' as the column name. Although you can use backticks to quote them, Reserved words are reserved for a reason.

Thats right, this will be changed.

What is your MySQL version?

On the other hand, this behavior has to be related to server code performance issue, so maybe you should issue a bug on MySQL community bugtracker. The dev guys should be able to give you a more accurate answer.

Its Version: ‘5.0.32-Debian_7-log’

I just wanted to make sure i did nothing stupid before bothering the developers, but may be i should give it a try.