CODEWITHSUNDEEP
mysqlsql
azekirel555
azekirel555

Reputation: 587

Single query VS Multiple query : execution time and resource usage

I'd like to know what are the downsides of using an "IN" restriction with a lot of values in it.

SELECT count(*), mail
FROM event, contacts
WHERE event.contactid = contacts.id
AND event_type = 1
AND mail IN (@1, @2, @3, etc.)
GROUP BY mail;

Also, do you think it would be better to split these queries into multiple ones that are executed in parallel ? What would be the consequences in terms of resource usage and execution time (for example) compared to the first solution ?

Thanks in advance

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

Qualify your column names and use table aliases:

SELECT count(*), c.mail  -- I am guessing `mail` comes from `contacts`
FROM event e JOIN
     contacts c
     ON e.contactid = c.id
WHERE e.event_type = 1 AND
      c.mail IN (@1, @2, @3, etc.)
GROUP BY c.mail;

There is no downside to having a large IN list (well, within reason -- at some point you may hit query length limits). In fact, MySQL has a nice optimization when constants are used for the IN list. It sorts the list and does a binary search on the values.

That said, if the list is coming from another table/query, then you should not put them in as constants. Instead, you should incorporate the table/query into this query.

Upvotes: 4

Related Questions