1

I want to create a nested query. I have a table named clicks with several columns among which are Datestamp, Keyword, Product_Title. The inner query is

SELECT DISTINCT(k.Keyword) 
FROM clicks k 
WHERE (k.Keyword LIKE '%word1%' AND k.Product_Title LIKE '%word2%');

Now I want to query for a bunch of rows such that the Keyword is one of the Keywords returned in the above query. I do the following:

SELECT c.Datestamp,c.Keyword,c.Product_Title 
FROM clicks c 
WHERE c.Keyword IN (
    SELECT DISTINCT(k.Keyword) 
    FROM clicks k 
    WHERE (k.Keyword LIKE '%word1%' AND k.Product_Title LIKE '%word2%')
);

Simple as this might seem, it doesn't work. The query just hangs there. No error message, no feedback whatsoever, just hangs there. I don't have that many rows, about 300k, so I'm sure the issue isn't the table being too big.

Suggestions?

Improve this question
6
  • Are you sure that you want an AND in your WHERE-Condition? The way you posted it here, there won't ever be a result because it's always false. Commented Aug 14, 2014 at 10:21
  • distinct is not a function Commented Aug 14, 2014 at 10:28
  • @schlonzo what? Yes there is. Keyword, Product_Title Commented Aug 14, 2014 at 10:54
  • @a_horse_with_no_name Like I said, the inner query runs perfectly. Commented Aug 14, 2014 at 10:55
  • I just wanted to point that out, because your usage of parentheses around the column indicates that you might have misunderstood how distinct works. distinct (col1), col2 is the same as distinct col1, col2 or distinct (col1, col2). distinct always operates on all columns in the select list and the parentheses will not change that behaviour. Commented Aug 14, 2014 at 11:03

2 Answers 2

Reset to default
1 
select distinct c.Keyword, c.Datestamp, c.Product_Title FROM clicks c
where c.Keyword   LIKE '%word1%' AND c.Product_Title LIKE '%word2%'
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

No, that's not the same.
0

Does your subquery run fast? Make sure you have an index on Keyword field it will make your query faster. Also try to use JOIN:

SELECT c.Datestamp,c.Keyword,c.Product_Title 
FROM clicks c 
JOIN
  (
    SELECT DISTINCT(k.Keyword) 
      FROM clicks k 
     WHERE (k.Keyword LIKE '%word1%') AND (k.Product_Title LIKE '%word2%')
  )  d on c.Keyword=d.Keyword

See MySQL Subquery Limitations for more info.

Improve this answer

3 Comments

Yes, this works, thanks. I would still like to understand why my query doesn't. If no one can explain it, then I will mark this answer as correct. Yes, the subquery runs fast, less than half a second.
@oneloop: MySQL Performance Blog
Ok, so I guess it doesn't really hang, it's just really really slow. I'm marking it as correct. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.