MySQL Performance: MySQL => slow log entry due to legitimate "Impossible WHERE"

Members

Help

Home

Home » Performance » MySQL » slow log entry due to legitimate "Impossible WHERE"

Show: Today's Messages :: Show Polls :: Message Navigator

slow log entry due to legitimate "Impossible WHERE" [message #1197]

Fri, 04 May 2007 06:53

jrmarino
Messages: 9
Registered: May 2007

Junior Member

I have turned on the mysql logging of slow queries + full joins. One of the logged queries produces this "extra" message using EXPLAIN: "Impossible WHERE noticed after reading const tables"

This is the query:
SELECT * FROM setup where season = 2007;

"season" is the primary key. The point of this query is to determine if the data row even exists.

So my problem is: I don't want this query logged in the slow-query log. Yes, I realize that you could write a query that NEVER succeeds (e.g. "WHERE 0") but in this case it could.

Is there anything I can do to prevent this particular type of query of being logged? A log file full of legitimate queries doesn't really help that much.

Thanks,
John

Report message to a moderator

Re: slow log entry due to legitimate "Impossible WHERE" [message #1199 is a reply to message #1197 ]

Fri, 04 May 2007 12:37

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

Have you investigated why you get a "Impossible WHERE" from that query?
Because it sounds very strange since that query is as straight forward as can be.

Report message to a moderator

Re: slow log entry due to legitimate "Impossible WHERE" [message #1200 is a reply to message #1197 ]

Fri, 04 May 2007 13:06

jrmarino
Messages: 9
Registered: May 2007

Junior Member

It's not a mystery. You get that error because 2007 is missing out of the primary index. It knows that because there is no entry in the primary index, this query can never succeed and gives that error.

However, it is legitimate that this primary entry may not be defined. Furthermore, it's really not a good error because you could insert a row later that would succeed.

Report message to a moderator

Re: slow log entry due to legitimate "Impossible WHERE" [message #1204 is a reply to message #1197 ]

Fri, 04 May 2007 16:49

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

Yes of course, but it only shows:
* When you are using MyISAM tables
* Have less than 2 rows in the table

And since I'm using InnoDB tables by default I didn't get that error.

No there doesn't exist any way to tell mysql to filter it out of the slow-query log.

But since you will never get that error with any query that actually takes any time to process you could just filter out that string when you are looking at the slow query log.

If you are on unix you can do something like this:

Quote:

grep -v 'Impossible WHERE noticed after reading const tables' [your_slow_query_log]

Because if you really have an impossible where then MySQL only reports "Impossible WHERE".

Or you can insert a couple of dummy rows in that table. It's not a nice solution but you will avoid the error message.

Report message to a moderator

Re: slow log entry due to legitimate "Impossible WHERE" [message #1206 is a reply to message #1204 ]

Fri, 04 May 2007 19:27

jrmarino
Messages: 9
Registered: May 2007

Junior Member

Well, it appears that you are misinformed because this is an innodb table, although in rare cases it may only have one row in it. I still believe what I read is true: It's based on the primary key as I said before. Other unrelated queries in the slow log support that as well.

As for the rest, if these legitimate queries can't be prevented from being put on the log in the first place, then I might as well turn off the logging other than those queries that really take a long time. It's just not very useful if it's going to log good queries as bad ones.

Report message to a moderator

Re: slow log entry due to legitimate "Impossible WHERE" [message #1210 is a reply to message #1197 ]

Sat, 05 May 2007 05:32

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

Right you are again, damn I feel like a Junior here. Wink

You really have to do a test matrix to test all outcomes to be certain of something. Smile

A unique index (like primary key) will always give the error that you are describing.
My guess is that if you have a normalized database and you are asking for something using the primary key it _should_ be there.
And if it is not then something is wrong hence error.

But if you use a _normal_ index things are a bit different.
* With less than 2 rows in the table.
MyISAM will always report "impossible where noticed ..."
InnoDB will only report "using where".
(Which I actually think is a bug because it is also reporting 1 rows and the table is empty).

* With 2 rows and more either one is not reporting anything.

Regarding what is useful in the log file an what is not it is of course up to you do decide.

But I think more information is better than no information.
Because it is so easy to filter out things afterwards, while it is impossible to find out what happened if it wasn't logged in the first place.

Try out using mysqldumpslow on your current log file with the "impossible where noticed" entries still left. See how much it affects what you see.

Report message to a moderator

Re: slow log entry due to legitimate "Impossible WHERE" [message #1212 is a reply to message #1210 ]

Sat, 05 May 2007 06:25

jrmarino
Messages: 9
Registered: May 2007

Junior Member

Quote:

My guess is that if you have a normalized database and you are asking for something using the primary key it _should_ be there.

The database is normalized, but there is no guarantee this row of data exists. The whole point of the query is to test if this data exists and react accordingly. The non-existence of this row does not indicate an error.

While it may not be up to me to say what should or should not be logged, I certainly believe there should be more granularity in the configuration file. I should be able to turn off logging of this type error.

I want the slow-query log to log problems, not grow to gigabyte size with normal queries or require filtering. As a user, I'm certainly entitled to decree something useful or not, as it's my point of view. It's current implementation is not very useful to me.

Report message to a moderator

Previous Topic:	Injecting large amounts of random data
Next Topic:	Terrible Performance... Will Pay For Help!

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 9 20:14:46 EDT 2009

Total time taken to generate the page: 0.00605 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.