Jump to content

Archived

This topic is now archived and is closed to further replies.

neoform

Impossible WHERE noticed after reading const tables

Recommended Posts

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?

[code]
+----+-------------+-------+------+---------------+-----+---------+-----+------+-----------------------------------------------------+
| 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 |
+----+-------------+-------+------+---------------+-----+---------+-----+------+-----------------------------------------------------+
[/code]

Share this post


Link to post
Share on other sites
Would you be so kind as to post the query?

Share this post


Link to post
Share on other sites
since you asked so nicely.. ;)

[code]
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'
[/code]

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

Share this post


Link to post
Share on other sites
Yup, that's why... the where clause on category will never be "used".

Share this post


Link to post
Share on other sites
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..

Share this post


Link to post
Share on other sites
I understand... but MySQL doesn't, as far as the indexing is concerned.  It's nothing to worry about.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
That's what I said... you can only use one index per table.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.