SHOW VARIABLES LIKE 'query_cache%';
Do not set query_cache_size to a large value. I recommend no more than 50M.
If you still have the problem at a smaller size, then set both of these:
query_cache_type = 0
query_cache_size = 0
To verify that the SELECT is doing what I expect, please provide the output from
EXPLAIN
SELECT b.shutDownFlag, b.time_of_ping, b.observedstate, b.simulatedstate
FROM
( SELECT max(time_of_ping) AS last_ping
from test100
where !(observedstate=4 and powercost=0)
) AS a
JOIN test100 AS b ON b.time_of_ping = a.last_ping ;
How many tables do you have? It sounds like you have lots of test### tables??
What values do you get for
SHOW VARIABLES LIKE '%open%';
In particular, do you have more test% tables than @@table_open_cache ?
If so, this could lead to a lot of opening/closing of tables.
If you would like, provide the following 3 things, and I will rummage around for other things that could be troublesome:
How much RAM;
SHOW VARIABLES;
SHOW GLOBAL STATUS;