SQL query optimization with INNER JOIN

Question

I got a challenge from school to optimise this query (this is theoretical question)

Challenge :

SELECT 
    n.node_id,
    MIN(LEAST(n.date,ec.date)) date
FROM 
    n, ec
WHERE 
    (n.node_id = ec.node_id_from OR n.node_id = ec.node_id_to)
    AND n.date - ec.date > 0
GROUP BY 
    n.node_id;

I make some effort to optimise this query and need to share with guys

SELECT   
    n.node_id, LEAST (n.date, ec.date) date
FROM   
    n 
INNER JOIN 
    ec ON (n.node_id = ec.node_id_from OR n.node_id = ec.node_id_to)
WHERE
    n.date - ec.date > 0

What I have done: I removed the min function and group by because least function only return least value from string.

Secondly I added the inner join with this query.

Is my solution fine or you have some more better option?

Tag your question with the database you are using. – Gordon Linoff Aug 19 '17 at 02:27 — Gordon Linoff, Aug 19 '17 at 02:27

LukStorms · Accepted Answer · 2017-08-19T08:54:34.363

When one wants to evaluate the equality of a single column against to 2 or more values then you can use IN instead of multiple OR's:

SELECT n.node_id, MIN(ec.date) as date
FROM n 
JOIN ec
  ON n.node_id IN (ec.node_id_from, ec.node_id_to) AND ec.date < n.date
GROUP BY n.node_id;

It's more concise, and there's a higher chance that the index on n.node_id will be used (if it exists).

Also, see this old post

And just using min(ec.date) instead of MIN(LEAST(n.date,ec.date)).
Because the JOIN already forces the ec.date to be lower than n.date anyway.

Also note that a where clause like

where (x >= y and x <= z)

can be changed to

where (x between y and z)

score 0 · Answer 2 · answered Aug 19 '17 at 02:29

0

or in an on clause is a performance killer. I imagine that you intend:

SELECT n.node_id, MIN(LEAST(n.date, ec.date)) date
FROM n join
     ec
     ON (n.node_id = ec.node_id_from OR n.node_id = ec.node_id_to) AND
        n.date > ec.date
GROUP BY n.node_id;

Note the parentheses.

If so, you can replace this with:

SELECT n.node_id,
       MIN(LEAST(n.date, COALESCE(ec.date, n.date), COALECE(ec1.date, n.date)) date
FROM n LEFT JOIN
     ec
     ON n.node_id = ec.node_id_from AND n.date > ec.date LEFT JOIN
     ec ec2
     ON n.node_id = ec.node_id_to  
GROUP BY n.node_id;

answered Aug 19 '17 at 02:29

Gordon Linoff

1,242,037
58
646
786

please justify the second query – Hafiz Usman aftab Aug 19 '17 at 02:31
1

if is work for or logic because you remove the `or` operator – Hafiz Usman aftab Aug 19 '17 at 02:36
1

and please tell me what is best solution for performance point of view soluton#1 or solution#2 ? – Hafiz Usman aftab Aug 19 '17 at 02:39
1

First solution is looking fine I think – usman Aug 19 '17 at 02:52
1

ok but i am waiting for @GordonLinoff responce what he suggest – Hafiz Usman aftab Aug 19 '17 at 02:53
@HafizUsmanaftab . . . The first query is what I suppose you intend. It is not the query that you write (note the parentheses). The downvote is rude. – Gordon Linoff Aug 19 '17 at 13:08

SQL query optimization with INNER JOIN

2 Answers2