Re: [sqlite] query trouble

[Dusty Burns]

I'm developing this for a tracker that will be designed to handle a lot of
Requests/s.. so I was wanting to optimize the queries as much as possible..
I'll try and run some more benchmarks and large result sets and see what I
get.. thanks for the input though.

On Wed, Sep 30, 2009 at 10:54 PM, Igor Tandetnik wrote:

> Dusty Burns wrote:
> > SELECT peers.compact, torrents.seeds, torrents.peers FROM torrents,
> > peers WHERE
> > torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
> > AND peers.info_hash=torrents.info_hash
> > LIMIT 50;
> >
> > which gives something like this
> >
> > peers.compact | torrents.seeds | torrents.peers
> > ---
> > data  | 1234   | 1234
> > data  | 1234   | 1234
> > data  | 1234   | 1234
> > data  | 1234   | 1234
> > data  | 1234   | 1234
> > etc...
> >
> > however, i'm getting torrents.seeds, torrents.peers with every row
> > returned which is redundant (possibly inefficient?)..
> > i only want the torrents.seeds, torrents.peers once.. the
> > peers.compact can be returned as usual
>
> If this bothers you so much, you can run two separate queries:
>
> SELECT torrents.seeds, torrents.peers FROM torrents
> WHERE torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17';
>
> SELECT peers.compact FROM peers
> WHERE peers.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
> LIMIT 50;
>
> I predict you'll find performance difference immeasurably small.
> --
> With best wishes,
>Igor Tandetnik
>
> With sufficient thrust, pigs fly just fine. However, this is not
> necessarily a good idea. It is hard to be sure where they are going to
> land, and it could be dangerous sitting under them as they fly
> overhead. -- RFC 1925
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query trouble

[Igor Tandetnik]

Dusty Burns wrote:
> SELECT peers.compact, torrents.seeds, torrents.peers FROM torrents,
> peers WHERE
> torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
> AND peers.info_hash=torrents.info_hash
> LIMIT 50;
>
> which gives something like this
>
> peers.compact | torrents.seeds | torrents.peers
> ---
> data  | 1234   | 1234
> data  | 1234   | 1234
> data  | 1234   | 1234
> data  | 1234   | 1234
> data  | 1234   | 1234
> etc...
>
> however, i'm getting torrents.seeds, torrents.peers with every row
> returned which is redundant (possibly inefficient?)..
> i only want the torrents.seeds, torrents.peers once.. the
> peers.compact can be returned as usual

If this bothers you so much, you can run two separate queries:

SELECT torrents.seeds, torrents.peers FROM torrents
WHERE torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17';

SELECT peers.compact FROM peers
WHERE peers.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
LIMIT 50;

I predict you'll find performance difference immeasurably small.
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query trouble

[Dusty Burns]

Been a while since using SQL of any type and I can't seem to remember how to
do a particular select.

At present, using something like this...

SELECT peers.compact, torrents.seeds, torrents.peers FROM torrents, peers
WHERE torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
AND peers.info_hash=torrents.info_hash
LIMIT 50;

which gives something like this

peers.compact | torrents.seeds | torrents.peers
---
data  | 1234   | 1234
data  | 1234   | 1234
data  | 1234   | 1234
data  | 1234   | 1234
data  | 1234   | 1234
etc...

however, i'm getting torrents.seeds, torrents.peers with every row returned
which is redundant (possibly inefficient?)..
i only want the torrents.seeds, torrents.peers once.. the peers.compact can
be returned as usual like so

peers.compact | torrents.seeds | torrents.peers
---
data  | 1234 | 1234
data  | null   | null
data  | null   | null
data  | null   | null
data  | null   | null
etc...

Anyone have any ideas? this would be appreciated.
btw, if this query could be optimized even further, i would welcome that.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users