On Monday, 15 April, 2019 13:31, Mohit Mathur wrote:
>I am working on one sqllite query, in which i am doing left outer
>join between two tables and than using intersect and again doing
>left outer join between two other tablescolumns that i am
>selecting are exactly same in number and dat
On 15 Apr 2019, at 8:31pm, Mohit Mathur wrote:
> Please let me know why it is throwing error.
What error ? Do you have an error number or text ?
If you perform the same SELECT in the SQLite command line tool do you get the
same error ?
___
sqlite-us
Hi tech gurus,
I am working on one sqllite query, in which i am doing left outer join
between two tables and than using intersect and again doing left outer join
between two other tablescolumns that i am selecting are exactly same in
number and datatypes.Please let me know why it is throwing e
>
> Would you care to repeat those two SELECTs, but after making indices on the
> X and Y columns ?
>
Simon, Tim, forgot to mention, there were also two indexes in the test db,
on X and on Y. Without them 1,8 seconds and 1/10 data flow would not be
possible ))
On Mon, Mar 22, 2010 at 2:52 PM, Tim
Another addendum: apologies -- I hope my
discussion was clear enough despite the disconnect between my head and
my fingers; I just noticed that I had typed "INNER JOIN" (yikes) rather
than "INNER LOOP", by which I mean fetching the rowids using an index
(on LATITUDE say) and then having to l
On 3/22/2010 7:32 AM, Tim Romano wrote:
> On 3/22/2010 2:15 AM, Max Vlasov wrote:
>
>>> Assuming a table where Latitude column and Longitude column each have
>>> their own index:
>>>
>>> perform select #1 which returns the rowids of rows whose latitude meets
>>> criteria
>>> INTERSECT
>>> perfo
On 3/22/2010 2:15 AM, Max Vlasov wrote:
>> Assuming a table where Latitude column and Longitude column each have
>> their own index:
>>
>> perform select #1 which returns the rowids of rows whose latitude meets
>> criteria
>> INTERSECT
>> perform select #2 which returns the rowids of rows whose lon
On 22 Mar 2010, at 6:15am, Max Vlasov wrote:
> Ok, just test.
>
> Created a base with a table
>
> CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT,
> [X] INTEGER,[Y] INTEGER)
>
> Filled with 1,000,000 records:
>
> INSERT INTO TestTable
> (X, Y)
> VALUES
> ((random() % 5) +
>
> Assuming a table where Latitude column and Longitude column each have
> their own index:
>
> perform select #1 which returns the rowids of rows whose latitude meets
> criteria
> INTERSECT
> perform select #2 which returns the rowids of rows whose longitude meets
> criteria
>
>
Ok, just test.
On 3/21/2010 5:22 PM, Max Vlasov wrote:
> On Sun, Mar 21, 2010 at 3:50 PM, Tim Romano wrote:
>
>
>> For someone who doesn't read C, could someone who knows please describe
>> the SQLite INTERSECT algorithm? What optimizations are available to it?
>> Does INTER
On Sun, Mar 21, 2010 at 3:50 PM, Tim Romano wrote:
> For someone who doesn't read C, could someone who knows please describe
> the SQLite INTERSECT algorithm? What optimizations are available to it?
> Does INTERSECT have to assume that neither vector is pre-sorted? Here's
>
On 3/21/2010 10:26 AM, Igor Tandetnik wrote:
> Tim Romano wrote:
>
>> For latitude/longitude queries
>>
> Without diving into the details of your situation, I wonder if you are aware
> of R-Tree extension:
>
> http://www.sqlite.org/rtree.html
>
Thank you Igor. I had read about R-TRE
Tim Romano wrote:
> For latitude/longitude queries
Without diving into the details of your situation, I wonder if you are aware of
R-Tree extension:
http://www.sqlite.org/rtree.html
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite
For someone who doesn't read C, could someone who knows please describe
the SQLite INTERSECT algorithm? What optimizations are available to it?
Does INTERSECT have to assume that neither vector is pre-sorted? Here's
the background of my question:
For latitude/longitude quer
On Mon, Jan 11, 2010 at 12:56 AM, D. Richard Hipp wrote:
>
> On Jan 10, 2010, at 4:50 AM, Max Vlasov wrote:
>
> > Documentation says that INTERSECT implemented with temporary tables
> > either
> > in memory or on disk. Is it always the case?
>
> No.
>
> If there is an ORDER BY clause, SQLite may
On Jan 10, 2010, at 4:50 AM, Max Vlasov wrote:
> Documentation says that INTERSECT implemented with temporary tables
> either
> in memory or on disk. Is it always the case?
No.
If there is an ORDER BY clause, SQLite may run each subquery as a
separate co-routine and merge the results. If t
Considering that INTERSECT is logically nothing but a special case of
relational join (the exact opposite of cartesian product), where all
columns are involved in the join condition, you should just be able to
reuse any optimizations that exist for join, including primary/unique
keys/etc. -- Darren
Documentation says that INTERSECT implemented with temporary tables either
in memory or on disk. Is it always the case? The problem is that if I have
several selects (six for example) when each produces thousands of results
and the intersection is only hundreds the query takes about minute to
execu
"Igor Tandetnik" schrieb im
Newsbeitrag news:hbfjnu$v...@ger.gmane.org...
> Olaf Schmidt wrote:
> > Just to add another one to the pile, any flaws I overlooked here...?
> >
> > select *, count(b) c_b from foo
> > where b in (...)
> > group by a
> > having c_b = length_of_b_list
>
> The OP apparen
Olaf Schmidt wrote:
> Just to add another one to the pile, any flaws I overlooked here...?
>
> select *, count(b) c_b from foo
> where b in (...)
> group by a
> having c_b = length_of_b_list
The OP apparently wanted actual (a, b) pairs for those a's that satisfy the
condition, not just a list of
"Pavel Ivanov" schrieb im
Newsbeitrag
news:f3d9d2130910170753k6e680ecdtcb892f05b21cc...@mail.gmail.com...
> > select * from foo f1 where
> > (select count(*) from (select distinct b from foo f2 where f1.a = f2.a
and f2.b in (...) )) =
> >length_of_b_list
> > and b in (...);
> Shouldn't this
> I wasn't sure SQLite supported count(distinct). The docs don't seem to
> mention it. But yes, apparently it does and the statement can be simplified
> this way.
Docs mention it though in very vague way. Here
http://www.sqlite.org/lang_aggfunc.html in the second paragraph:
"In any aggregate fu
Pavel Ivanov wrote:
>> select * from foo f1 where
>> (select count(*) from (select distinct b from foo f2 where f1.a = f2.a and
>> f2.b in (...) )) =
>>length_of_b_list
>> and b in (...);
>
> Shouldn't this be simplified like this?
>
> select * from foo f1 where
> (select count(distinct b) f
> select * from foo f1 where
> (select count(*) from (select distinct b from foo f2 where f1.a = f2.a and
> f2.b in (...) )) =
>length_of_b_list
> and b in (...);
Shouldn't this be simplified like this?
select * from foo f1 where
(select count(distinct b) from foo f2 where f1.a = f2.a and f2
P Kishor wrote:
> I don't even know how to title this post, and it just might be
> something very obvious. Either way, I apologize in advance. Consider
> the following --
>
> sqlite> SELECT * FROM foo;
> a b
> -- --
> 1 6
> 2 6
> 2 3
> 3
P Kishor wrote:
> I want the values of ‘a’ for which ‘b’ = 3 AND ‘b’ = 4 (This is just
> an example. In reality, b could be any set of numbers, not just two
> numbers). To illustrate --
> sqlite> SELECT * FROM foo WHERE b = 3;
> a b
> -- --
> 2 3
> 3
I don't even know how to title this post, and it just might be
something very obvious. Either way, I apologize in advance. Consider
the following --
sqlite> SELECT * FROM foo;
a b
-- --
1 6
2 6
2 3
3 3
3 4
3 5
4
27 matches
Mail list logo
