CodingForums.com - View Single Post

Old Pedant · 01-23-2013, 08:12 PM

Still getting that "impossible where". Which means that if you really run the query you should get zero results. Do you?

And by the by, I misread your EXPLAIN in your post #3 !! Your query *IS* using a key. It's just that the key length is so long it's not a very efficient key. But it probably can't do any better. And then, as I said in my post #4, I would *guess* that the reason for the USING TEMPORARY stems from your DISTINCT usage.

In order to perform DISTINCT, MySQL has to sort *ALL* the records (after the JOIN and WHERE are satisified) on *ALL* the fields you specified in your SELECT. Because you included pac2_notifications.*in the SELECT, that means it has to sort on all of those fields. After doing the sort, *then* it runs through all the fields looking for any *EXACT* matches and rejecting all but the first such exact match.

In other words, DISTINCT is a very very expensive option that should be avoided when possible.

01-23-2013, 08:12 PM	PM User \| #9
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,168 Thanks: 59 Thanked 3,993 Times in 3,962 Posts	Still getting that "impossible where". Which means that if you really run the query you should get zero results. Do you? And by the by, I misread your EXPLAIN in your post #3 !! Your query IS using a key. It's just that the key length is so long it's not a very efficient key. But it probably can't do any better. And then, as I said in my post #4, I would guess that the reason for the USING TEMPORARY stems from your DISTINCT usage. In order to perform DISTINCT, MySQL has to sort ALL the records (after the JOIN and WHERE are satisified) on ALL the fields you specified in your SELECT. Because you included pac2_notifications.in the SELECT, that means it has to sort on all of those fields. After doing the sort, then* it runs through all the fields looking for any EXACT matches and rejecting all but the first such exact match. In other words, DISTINCT is a very very expensive option that should be avoided when possible. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.