On 29 Nov 2016, at 9:41pm, Kevin Baggett wrote:
> Here's the query:
> SELECT a.observation_day, a.observation_hour, a.observation_time,
> a.text_file_name, a.start_byte, a.message_length, a.wmo_header, a.wmo_prefix,
> max(a.rmkcorr_flag),b.wmo_ID,b.latitude,b.longitude from main.file_list a,
Hi,
I am trying to speed up a query on my SQLite database using SQLite
version 3.7.17 2013-05-20 00:56:22.
The daily database has 2 tables: file_list and station_list.
Yesterday's database had a file_list table of over 1.7 million records.
station_list is pretty much constant at 21549 records.
On 8/26/06, Pablo Santacruz <[EMAIL PROTECTED]> wrote:
Try something like this:
CREATE INDEX ix_polys ON polys (xmin, ymin, xmax, ymax);
SELECT name
FROM polys
WHERE EXISTS
(SELECT x FROM points WHERE point_id = 1 AND xmin < x AND ymin < y AND xmax
> x AND ymax > y);
In your query you do 4 sele
Try something like this:
CREATE INDEX ix_polys ON polys (xmin, ymin, xmax, ymax);
SELECT name
FROM polys
WHERE EXISTS
(SELECT x FROM points WHERE point_id = 1 AND xmin < x AND ymin < y AND xmax
x AND ymax > y);
In your query you do 4 select from points table. Here, you do only one. I
think thi
On 8/26/06, Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- P Kishor <[EMAIL PROTECTED]> wrote:
..
> Actually, I have tried many other approaches, and SQL seems to be a
> fairly efficient one. Here is why -- in the worst case, I have to test
> each point against each poly (a geometry algorithm allo
--- P Kishor <[EMAIL PROTECTED]> wrote:
> On 8/26/06, Joe Wilson <[EMAIL PROTECTED]> wrote:
> > The sample poly.name's you've provided in your example seem to be integers.
> > Is that always the case? Are the poly_id's unique? If both of these
> > are true, you might consider making poly_id your pr
On 8/26/06, P Kishor <[EMAIL PROTECTED]> wrote:
On 8/26/06, Cory Nelson <[EMAIL PROTECTED]> wrote:
> Instead of indexing each column on its own, try making them one big index.
>
Thanks. Actually making a composite index really helped. The match
rate went from 2.5/sec to more than 82/sec. Very n
On 8/26/06, Cory Nelson <[EMAIL PROTECTED]> wrote:
Instead of indexing each column on its own, try making them one big index.
Thanks. Actually making a composite index really helped. The match
rate went from 2.5/sec to more than 82/sec. Very nice. I did the
following --
sqlite> CREATE INDEX
The sample poly.name's you've provided in your example seem to be integers.
Is that always the case? Are the poly_id's unique? If both of these
are true, you might consider making poly_id your primary key in the
polys table in order to eliminate a column.
This problem is very difficult to do in
Greets,
Using SQLite for Windows 3.3.7. I have the following two tables
-- bounding box of each poly
CREATE TABLE polys (
poly_id INTEGER PRIMARY KEY,
xminREAL,
yminREAL,
xmaxREAL,
ymaxREAL,
nameTEXT
)
data look like so
1|1723885.18957644|282631.95646140|1727224.465378
10 matches
Mail list logo
