0

I want to optimise my subquery. From the mysql doc i found.

SELECT * 
FROM t1 
WHERE t1.column1 IN   ( SELECT column1 
                        FROM t2 
                        ORDER BY column1 );

SELECT * 
FROM t1 
WHERE t1.column1 IN  ( SELECT DISTINCT column1 
                       FROM t2
                      ); 
                      
SELECT * 
FROM t1 WHERE EXISTS   ( SELECT * 
                         FROM t2 LIMIT 1
                         );

I was able to achieve this format using this objection js code.

Person.query()
  .from(
    Person.query()
      .select(
        'persons.name as persons_name',
        'persons.disclaimer as persons_disclaimer',
        'persons.id as persons_id'
      )
      .as('optimised')
      .limit(40)
  )
  .select('optimised.*')
  .select((qb) => {
    qb.select(raw(`sum(act.count)`))
      .from('activity as act')
      .where('act.p_id', '=', 'optimised.persons_id')
      .as('actCountSum');
  })
  .select((qb) => {
    qb.select(raw(`count(*)`))
      .from('activity as act')
      .where('act.p_id', '=', 'optimised.persons_id')
      .as('actCount');
  })
  .debug();

But the problem is i am getting null and 0 respectively because on where clause its passing optimised.persons_id as a string.

Any solution?

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
abhishekbutola
  • 59
  • 1
  • 9

1 Answers1

1

The 3rd one looks simply wrong.

Try this:

SELECT  *
    FROM  t1
    WHERE  EXISTS (
        SELECT  1
            FROM  t2
            WHERE  t1.column1 = t2.column1 
                  );

If there aren't dups, then do simply

SELECT  t1.*
    FROM  t1
    JOIN  t2  ON t1.column1 = t2.column1
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I thought that too but. See the mysql docs first example https://dev.mysql.com/doc/refman/5.7/en/optimizing-subqueries.html – abhishekbutola Jan 28 '22 at 03:12
  • @abhishekbutola - I'll stick my neck out and say that that trio is garbage. And they don't do the same thing. The third one _semantically_ only checks that t2 has at least 1 row -- That is, you either get all of t1 or the empty set. Is that what you want? – Rick James Jan 28 '22 at 03:40
  • Yep. you are right that is what the objectionjs generated without the third select. I am new to this sql so i thought subqieries need to be optimised according to docs. – abhishekbutola Jan 28 '22 at 04:13
  • One more quick question i want to ask. Is there any huge performance difference in doing select count(*) query vs select count(*) from (select things query)? – abhishekbutola Jan 28 '22 at 04:16
  • 1
    @abhishekbutola - There are so many variations on subqueries that I can't make any general statements. As for the count()... Don't bother fetching the "things" if all you will do with them is count them. That is probably an example of where the Optimizer fails to simplify the query, and it is up to the programmer to do it. – Rick James Jan 28 '22 at 15:09