1

I am experiencing a strange thing while excecuting an sql query

SELECT *
FROM clients
WHERE id IN
    (SELECT DISTINCT clientId
     FROM quotes
     WHERE storeEmail LIKE '%something@example.net%')

Now Result of SELECT DISTINCT clientId FROM quotes WHERE storeEmail LIKE '%something@example.net%' has only few items like 10-20 but when i run above query, the entire phpmyadmin stucks and database starts to crash. What is wrong?

Sadikhasan
  • 18,365
  • 21
  • 80
  • 122
Muhammad Umar
  • 11,391
  • 21
  • 91
  • 193
  • how many entries in total does the table have? – Top Questions Jul 12 '14 at 04:33
  • 2
    That email address looks complete. Why are you using `LIKE` when you could just use `=`? That could then be indexed and searched more quickly. –  Jul 12 '14 at 04:38
  • Well, i have tried = too, the entries are like 20k maybe but SELECT DISTINCT clientId has 20k entries too and its super fast like 2-3 seconds at max – Muhammad Umar Jul 12 '14 at 04:45
  • Please don't post real email addresses; they will get scraped by spammers and spammed into oblivion. – elixenide Jul 12 '14 at 04:52
  • Do you have an index on the `storeEmail` column? Please post the results of `EXPLAIN your_query_here`. – elixenide Jul 12 '14 at 04:53

1 Answers1

0

Try using EXISTS. According to this post, it's faster than IN for very large subqueries

SELECT * FROM clients c
WHERE EXISTS (
    SELECT * FROM quotes q
    WHERE c.id = q.clientId
    AND q.storeEmail = 'something@example.net'
)

You'll also need a composite index on quotes(clientId,storeEmail)

Community
  • 1
  • 1
FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
  • this might work, but you didn't tell why the actual code of OP is not working. Also you are doing `select * from quotes` – spiderman Jul 12 '14 at 05:00
  • @prash for `EXISTS` it doesn't matter what you select, see https://stackoverflow.com/questions/6137433/where-does-the-practice-exists-select-1-from-come-from – FuzzyTree Jul 12 '14 at 05:03