MyBB Community Forums

Full Version: Impossible Where
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I have been trying to find out possible causes for the slowness of my board, and I have a question about a line I saw in the MyBB Debug Information (this result came from a page I loaded in a forum with admin access only)

#30 - Select Query
SELECT tid, name, type FROM mybb_modtools WHERE CONCAT(',',forums,',') LIKE '%,20,%' OR CONCAT(',',forums,',') LIKE '%,-1,%' OR forums=''
table	type	possible_keys	key	key_len	ref	rows	Extra
Impossible WHERE noticed after reading const tables
Query Time: 0.79508996009827

I have also seen that impossible where when looking at the index, referring to a couple of extra usergroups I have set up.

My first question is, is this "impossible WHERE" one of the causes for delay on page loads?

Also, second question, do the queries run sequentially, and if so is there any way we can control that?
That's weird, the query is the same here, just with different numbers, and it doesn't say impossible WHERE:

SELECT tid, name, type FROM mybb_modtools WHERE CONCAT(',',forums,',') LIKE '%,127,%' OR CONCAT(',',forums,',') LIKE '%,-1,%' OR forums=''

0.7 seconds is pretty slow though, how long are other queries running for??

And the queries are run in whatever order they're called in the code, not really sure what you could do to control that...
Well, I just tried it again, on a different thread in the same forum. Same "impossible where" showing up. I have it on the usergroups as well :

#12 - Select Query
SELECT COUNT(uid) as total FROM mybb_joinrequests WHERE gid IN (0,12,11)
table	type	possible_keys	key	key_len	ref	rows	Extra
							Impossible WHERE noticed after reading const tables
Query Time: 0.00087404251098633

Regarding the query time - well thats a bit odd too. It seems to do it the worst on the first page load of the day. I do see that there is some kind of correlation between the amount of php used to load a page and the MYSQL.

Like for the page above, query time:

Generated in 3.1997561 seconds (3.23% PHP / 96.77% MySQL)

I have some occasions where the php is like .5% and the page takes eternity to load. (longest load time recorded by me is 66 seconds). Of course, its shared hosting, I am now on the "performance server"(hollow laughter) and in the process of moving to my friends VPS....

But I thought it does no harm to try and find the longest queries (if you see what I mean) and try and optimize them - and most of those seem to be about session ids, forum permissions etc. Thats core mybb, so I don't go fiddling with that.

If you think its an interesting problem, and not just host crappiness, I can give you admin permissions (ftp and phpmyadmin as well if you think its needed).
As far as I know, an "impossible WHERE" in EXPLAIN means that no rows were returned at all. If you just ran the query, it would return nothing but in EXPLAIN it returns an "impossible WHERE".
You may want to take a look at here:
http://forums.bit-tech.net/showthread.php?t=158175

Do you have many mod tools? Because that query is just taking too long to get executed.
I have no mod tools. I just have the standard stuff.

The thing is - don't get distracted by the length of time it took to run that query - I can run the SAME query (like load the same page or a similar page from the same forum) 5 minutes later and its super fast and comes in under the .000 seconds. The query that is, not the page.
(2010-12-22, 11:45 AM)Leefish Wrote: [ -> ]I have no mod tools. I just have the standard stuff.

The thing is - don't get distracted by the length of time it took to run that query - I can run the SAME query (like load the same page or a similar page from the same forum) 5 minutes later and its super fast and comes in under the .000 seconds.

Alright, I guess your question has now been answered then. (no entries were found because this query probably searches for "automatic tools", that new feature added in MyBB 1.6) (for the second query, there seem to be no new join requests)
Hi Pirata - so from reading that link, its not a performance issue, and so I will plod on trying to find out why I have such a huge variation in page load times.

Any tips on why the amount of php vs mysql used can vary so much in the debug info (the little data thing in the bottom right of every page when viewing as an admin)? Or am I taking that too literally?
It is possible you're getting such high query times from a bad mysql server. If the problem persists after the move to vps you can look into it again (with a VPS you'll have better logs access than most shared hosts too) and see whats going on.
Thanks Dylan, I will look into that once the move is complete Smile