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
