How to Optimize Subquery in Objection JS?

Question

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?

The LIMIT clause causes enough difference that you cannot use the "tips" in the ref manual. (They did not include LIMIT.) — Rick James
– Rick James, Commented Jan 28, 2022 at 3:41
Your 3rd query seems invalid since tehre is no mention of the table optimized. — Rick James
– Rick James, Commented Jan 28, 2022 at 3:43
I think the bottom line is: You can't trust "tips" from one RDBS (eg MySQL) for optimizing some other RDBS (eg, Objection.js) — Rick James
– Rick James, Commented Jan 28, 2022 at 3:48

Rick James · Accepted Answer · 2022-01-28 00:00:52Z

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

answered Jan 28, 2022 at 0:00

Rick James

144k15 gold badges144 silver badges254 bronze badges

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

5 Comments

abhishekbutola Over a year ago

I thought that too but. See the mysql docs first example dev.mysql.com/doc/refman/5.7/en/optimizing-subqueries.html

Rick James Over a year ago

@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?

abhishekbutola Over a year ago

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 Over a year ago

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)?

Rick James Over a year ago

@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.

Collectives™ on Stack Overflow

How to Optimize Subquery in Objection JS?

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related