MySQL SubQueries Multiple Results

Question

I have a query as follows:

SELECT event 
FROM log
WHERE user = (SELECT SUBSTRING(details,55) FROM log WHERE details LIKE 'ID: 308%')

I know I can use an inner join or php loop separate here but I have queries where I cannot use inner joins and a problem similar to this happens (which im about to explain).

The subquery for the where clause returns many email addresses, and I want to then bring up any log events relating to any of them. My problem is I then get an error message 'Subquery returns more than 1 row'.

If anyone could help that would be much appreciated!

mavrosxristoforos · Accepted Answer · 2013-10-14 16:13:48Z

3

I think this should work:

SELECT event FROM log
WHERE user IN
  (SELECT SUBSTRING(details,55) FROM log WHERE details LIKE 'ID: 308%')

answered Oct 14, 2013 at 16:13

mavrosxristoforos

3,6833 gold badges27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James P. Over a year ago

IN is the usual way to do it.

Barmar · Accepted Answer · 2013-10-14 16:21:15Z

2

Use WHERE user IN <subquery> instead of WHERE user = <subquery>.

However, in my experience, MySQL's performance of IN <subquery> is usually very poor. This can always be rewritten as a JOIN, and that usually performs better.

Here's your example query rewritten as a JOIN:

SELECT event
FROM log l1
JOIN (SELECT DISTINCT SUBSTRING(details,55) loguser
      FROM log
      WHERE details LIKE 'ID: 308%') l2
ON l1.user = l2.loguser

In this particular case, I suspect performance will be similar. Where MySQL usually gets it wrong is when the subquery in the JOIN returns an indexed column, and you're joining with an indexed column in the main table. MySQL decides to use the index in the subquery instead of the main table, and that's usually wrong (in most cases of queries like this, the subquery returns a small subset of values).

edited Oct 14, 2013 at 16:21

answered Oct 14, 2013 at 16:14

Barmar

789k57 gold badges555 silver badges669 bronze badges

1 Comment

James P. Over a year ago

How would you replace the IN with a JOIN ?

AgRizzo · Accepted Answer · 2013-10-14 16:59:36Z

0

Your other option is to use EXISTS

SELECT `event`
FROM log
WHERE EXISTS(SELECT *
             FROM log AS log1
             WHERE log1.details LIKE 'ID: 308%'
               AND log.user = SUBSTRING(log1.details,55))

answered Oct 14, 2013 at 16:59

AgRizzo

5,2711 gold badge15 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL SubQueries Multiple Results

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related