Erroneous queries = performance degradation

MySQL & MariaDB Other MySQL® Questions
1

Yesterday we observed a great performance degradation on our server. The processor load was about 100% and it was almost impossible to log into the system using MyAdmin.
Later investigation revealed that at that time somebody started a script with a lot of incorrect queries. Simple SELECTs with incorrectly typed field. All these queries appeared in slowquery log, with typical time of 2 seconds per query.
Why it took that long ?

2

As an owner of a crystal ball I’m going to try to explain what went wrong ;).
It is much easier if you can post an error message and the actual query.

But my guess is that you had queries something like:
1.
JOIN without a proper join condition which means that you can get a cross join which makes the table very big.

A function encapsuling the column in a comparison, which means that every record in the table has to be processed before evaluating the condition, hence no index can be used.

3

Your crystal ball probably needs some maintenance. As I already wrote, there were simple queries. That means :

SELECT sum(count_sale) FROM stat WHERE seller = ‘whatever’ AND date_sale = ‘2007-10-24’ GROUP BY date_sale;

And the error was

Unknown column ‘seller’ in ‘where clause’

Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 3193919

Why incorrect query still examines some rows ?
Isn’t it clear that if column in where clause does not exist,
the query will not be processed.

4
[B]AlexN wrote on Fri, 09 November 2007 12:13[/B]

Isn’t it clear that if column in where clause does not exist,
the query will not be processed.

Yes it is, so that's why I have a hard time thinking that this is the query with the problem. MySQL will never start processing any rows since it bails out during the execution plan planning stage. The figure of rows could just be the total nr of rows in the table and does in this case not necessarily have anything to do with how many rows that actually was processed.

The only scenario I can think off when you get a delay of an error message like this is if the table is locked by another thread.
Since your thread needs access to the table definition to recognize that the column does not exist.

My suggestion is keep digging.