Re: [sqlite] Optimisation opportunity on subquery?

Jonathan Moules Wed, 02 Jan 2019 15:59:24 -0800

Gah, sorry. Another typo. I really should be more awake when I post tothis list. The non-simplified code does have the item_id on the subquery(otherwise it simply wouldn't execute at all of course). So:


SELECT *
  FROM item_info
  JOIN (
    select
      count(1) as num,
      item_id
    from users
    group by item_id)
 USING (item_id)
 where item_id = ?;




On 2019-01-02 22:56, Keith Medcalf wrote:

When you are executing the query:

SELECT *
   FROM item_info
   JOIN (select count(1)
           from users
       group by item_id)
  USING (item_id)
  where item_id = ?;

You are telling the SQL Database Engine (whatever it may happen to be, in this 
case SQLite3) that you want to take the table produced by:

select * from item_info;

and join it (an equijoin) against the table produced by running the query:

select count(1) from users group by item_id;

based on the equality of the column item_id in both tables.  If you run the two 
queries you will see that the second table DOES NOT produce a column called 
item_id.  Therefore you cannot join those tables and instead you get an error 
message telling you that the column item_id does not exist in both tables.

FROM tables are siblings not correlates.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

-----Original Message-----
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Jonathan Moules
Sent: Wednesday, 2 January, 2019 15:21
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Optimisation opportunity on subquery?

Hi Simon,

Thanks for that - I'm always trying to improve my SQL. I think I see
what you mean now.

Assuming my original query:

     SELECT
         *
     FROM
         item_info
     JOIN (select count(1) from users group by item_id)
     USING (item_id)
     where item_id = ?;

There are three uses of "item_id".

* The first is the "group by", which unless my SQL is even worse than
I
imagine, can only be referencing the users table.

* The second is "USING" - which is referencing both.

* The third is the one I guess you mean is ambiguous? My thinking was
that because item_id is going through the USING it wasn't ambiguous
as
they're the same thing; though that's apparently not how the query
planner sees it, and hence your reference to ambiguity. That right?

So I tried using aliases (I'm assuming that removes the ambiguity),
but
the query times remained at about 0.5s for both versions (whether
i.item_id or u.item_id):

     SELECT
         *
     FROM
         item_info i
     JOIN (select count(1) from users group by item_id) u
     USING (item_id)
     where u.item_id = ?;

Thanks again for clarifying, but after checking, it doesn't seem like
it
was an ambiguity thing.
Cheers,
Jonathan

On 2019-01-02 22:04, Simon Slavin wrote:

On 2 Jan 2019, at 9:50pm, Jonathan Moules <jonathan-

li...@lightpear.com> wrote:

Sorry, but which column is ambiguous? The users.item_id is a

foreign key to the item_info.item_id - that's why it's a "REFERENCES"
- why would I want to change it to be something else? Isn't the
convention for FK's to have the same name across tables? That's what
"USING" is for right? (or NATURAL, but I prefer to be explicit.)
Happy to be corrected.

It may be that our careers developed with different ideas about how

to use SQL.  You had a JOIN, both tables had a column "item_id", and
a reference inside the JOIN to "item_id" would be ambiguous.  Since
the SQL standard does not make it clear which table would be used, it
would be possible for different implementations of SQL to think you
meant different tables.

I understand that, in your example, the values would be the same.

But that doesn't explain to you what the optimizer thinks you're
trying to do.  The simplest way to tell the optimizer what you need
would be to rename one of the columns.  You could try both tables,
see which solution was faster, and use that one.

However, I see other posters have gained better clarity for your

problem.

Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-

users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimisation opportunity on subquery?

Reply via email to