Mysql crashing and slowing down over simple query run

Question

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 '%[email protected]%')

Now Result of SELECT DISTINCT clientId FROM quotes WHERE storeEmail LIKE '%[email protected]%' 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?

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

score 0 · Accepted Answer · edited May 23 '17 at 10:32

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 = '[email protected]'
)

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

edited May 23 '17 at 10:32

Community

1
1

answered Jul 12 '14 at 04:55

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

Mysql crashing and slowing down over simple query run

1 Answers1