On Wednesday, 2 January, 2019 16:58, Jonathan Moules
wrote:
>Gah, sorry. Another typo. I really should be more awake when I post
>to this 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
, 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
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] Optimi
thinking of at the moment. So it becomes a not so
>trivial thing to identify and use safely.
>
>
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Wednesday, January 02, 2019 3:34
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
On 2 Jan 2019, at 9:50pm, Jonathan Moules 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
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
s-boun...@mailinglists.sqlite.org] On
Behalf Of Simon Slavin
Sent: Wednesday, January 02, 2019 3:34 PM
To: SQLite mailing list
Subject: Re: [sqlite] Optimisation opportunity on subquery?
On 2 Jan 2019, at 4:44pm, Jonathan Moules wrote:
> SELECT
> *
> FROM
> it
On 2 Jan 2019, at 4:44pm, Jonathan Moules wrote:
> SELECT
> *
> FROM
> item_info
> JOIN (select count(1) from users group by item_id)
> USING (item_id)
> where item_id = ?;
You have an ambiguous column name, and I don't think SQLite is doing what you
think
-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Jonathan Moules
>Sent: Wednesday, 2 January, 2019 09:44
>To: SQLite mailing list
>Subject: [sqlite] Optimisation opportunity on subquery?
>
>Hi List,
>
>The below seems to my very-non-expert mind like there's scope for
>q
Hi List,
The below seems to my very-non-expert mind like there's scope for
query-plan optimisation.
I have two tables (simplified below):
CREATE TABLE users (
item_id TEXT REFERENCES item_info (item_id)
NOT NULL
COLLATE NOCASE,
some_data
11 matches
Mail list logo
