Do I correctly understand the intention of the UPDATE is that for
each my_id in meta_table, it will store the count of all the hashes that
are associated only with my_id and no other id's?
In that case, have you tried:
UPDATE
meta_table
SET
distinct_hashes = (
SELECT
I checked with a fake dataset: the GROUP BY solution is quicker with no
index on F2 (the query planner can understand that query well enough to
create an automatic covering index). However, with an index on F2, the
rowid solution is considerably faster for a dataset with 2600 random
distinct F2
Ryan, my dataset isn't that big. 11K records.
Your solution is noticeable faster 996 ms vs 13126 ms.
Interesting!
gert
Op za 23 jun. 2018 om 18:09 schreef R Smith :
>
> On 2018/06/23 2:47 PM, Gert Van Assche wrote:
> > Barry, that's even easier indeed. And it works perfect!
> > Thanks for
On 23 Jun 2018, at 5:08pm, R Smith wrote:
> May I ask, if you do have a rather large dataset, and perhaps don't mind the
> time, would you care to compare the two methods and let us know if there is
> any discernible difference in speed?
Answers will be different depending on type of main
On 2018/06/23 2:47 PM, Gert Van Assche wrote:
Barry, that's even easier indeed. And it works perfect!
Thanks for sharing this.
I very much like the method which Barry suggested for the simplicity,
but have avoided it since I thought it would be significantly slower on
a large data set,
Hi List,
I'm trying to find all hashes that are unique to a specific id (my_id),
and then use a UPDATE-Join to update another table with that number.
After much tweaking, I've simplified the table down to a basic temp
table (actually created using a CREATE AS SELECT ... GROUP BY my_id, hash):
Barry, that's even easier indeed. And it works perfect!
Thanks for sharing this.
gert
Op za 23 jun. 2018 om 14:32 schreef Barry Smith :
> Ryan's way works well. Here is a second method which expresses it in a
> different way:
>
> SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE
Ryan's way works well. Here is a second method which expresses it in a
different way:
SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 = t2.F2
ORDER BY rowid LIMIT 10)
If you have WITHOUT ROWID tables you'd have to replace rowid with your primary
key.
(The query may
Hi Ryan, thanks for this. This is working if the F1 field is a numeric
value. With text (especially Asian & Arabic characters) this does not seem
to work.
So I created an MD5 hash from the text fields and it works great! Thank you
so much.
gert
Op vr 22 jun. 2018 om 22:52 schreef R Smith :
>
>
9 matches
Mail list logo
