Jump to content


Photo

Impossible WHERE noticed after reading const tables


  • Please log in to reply
9 replies to this topic

#1 neoform

neoform
  • Members
  • PipPipPip
  • Advanced Member
  • 241 posts
  • LocationMontreal

Posted 30 October 2006 - 03:02 AM

I realize that mysql says it's useless to be there, but I'm using this as a check to make sure that both those fields are applicable to the row I'm selecting.. 

question is, does this have a negative effect in anyway?

+----+-------------+-------+------+---------------+-----+---------+-----+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra                                               |
+----+-------------+-------+------+---------------+-----+---------+-----+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL| NULL    | NULL| NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+-----+---------+-----+------+-----------------------------------------------------+

Newsique.com Social News Network

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 October 2006 - 05:04 PM

Would you be so kind as to post the query?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 neoform

neoform
  • Members
  • PipPipPip
  • Advanced Member
  • 241 posts
  • LocationMontreal

Posted 30 October 2006 - 05:21 PM

since you asked so nicely.. ;)

SELECT 
			id, 
			url_name, 
			name, 
			category, 
			type, 
			body, 
			description, 
			url, 
			source, 
			source_id, 
			location, 
			posted_on, 
			user_id, 
			votes, 
			credibility,
			misquote  
		FROM 
			seeds 
		WHERE 
			category = 'world' 
		AND 
			url_name = 'moo'

url_name is a unique field (unique index, not primary key), which is why it's probably saying that..

Newsique.com Social News Network

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 October 2006 - 06:20 PM

Yup, that's why... the where clause on category will never be "used".
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 neoform

neoform
  • Members
  • PipPipPip
  • Advanced Member
  • 241 posts
  • LocationMontreal

Posted 30 October 2006 - 06:39 PM

well i apply the category as more of a qualifier..  basically making sure that the row being selected matches both requirements..  url_name AND category..  i don't want a result being returned if the category does not match that row even if the const field matches..
Newsique.com Social News Network

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 October 2006 - 07:24 PM

I understand... but MySQL doesn't, as far as the indexing is concerned.  It's nothing to worry about.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 craig1972

craig1972
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 08 December 2006 - 06:03 AM

Hi,

It *is* something to worry about as it significantly reduces the speed of the query, as no index is used.

What's the solution to this? Let's say I have a column COL1 in my db. This needs to be unique. But in conjunction with COL2, this also needs a separate index. How should I tell MySQL that it's OK to have the two indices and use both of them in queries?

I am using MySQL 4.1.21-standard-l.

Thanks!

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 December 2006 - 02:41 AM

Well, if you use a subquery, you can have each query use a different index -- only MySQL 5 is smart enough to use both for a single query.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 craig1972

craig1972
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 09 December 2006 - 06:09 AM

Where did the notion of a subquery come in? I only want to know what to do with a column having both UNIQUE index and an INDEX index.

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 December 2006 - 11:28 PM

That's what I said... you can only use one index per table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users