Re: [sqlite] Question on Queries
Thanks for everybody's input, I will test these things out tonight... On Mon, Mar 3, 2008 at 3:53 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > > SELECT data FROM LIST l > > INNER JOIN MAIN m ON l.mid = m.id > > WHERE m.name = "something"; > > The two statements are not equivalent: they produce different results if > there's more than one record in MAIN with name='something' > I guess this was one criteria that was not specified. 'name' is intended to be unique as well. Should I redeclare my table to reflect as much? Would that improve performance any? Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Queries
Scott Baker <[EMAIL PROTECTED]> wrote: > Mike McGonagle wrote: >>> -- Compound Query >>> SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = >>> "something") ORDER BY ord; >>> >>> -- Individual Queries >>> SELECT id FROM MAIN WHERE name = "something"; >>> SELECT data FROM LIST WHERE mid = id_as_returned_above; > > This just screams inner join. > > SELECT data FROM LIST l > INNER JOIN MAIN m ON l.mid = m.id > WHERE m.name = "something"; The two statements are not equivalent: they produce different results if there's more than one record in MAIN with name='something' Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Queries
I'm sure the real experts will chime-in, but it looks like you might be executing the subquery once for every row in main. Maybe if you use a join, it would go faster select L.data from list L, main m where m.name='something' and L.mid = m.id; Or, maybe you could just use in() rather than =. -- Compound Query SELECT data FROM LIST WHERE mid in (SELECT id FROM MAIN WHERE name = "something") ORDER BY ord; -Clark - Original Message From: Mike McGonagle <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Monday, March 3, 2008 1:32:45 PM Subject: [sqlite] Question on Queries Hello all, I was working with some queries last night, and ran accross something that I don't quite understand. Basically, this is what I have... *** CREATE TABLE MAIN ( id integer primary key autoincrement not null, name varchar(30), [other fields left out, as they are not used] ); CREATE TABLE LIST ( mid integer, ord integer, data float ); -- Compound Query SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = "something") ORDER BY ord; -- Individual Queries SELECT id FROM MAIN WHERE name = "something"; SELECT data FROM LIST WHERE mid = id_as_returned_above; *** So, what is happening is when I run the first query, it takes about 45 seconds for the data to be returned. It is correct and everything, just takes a long time. But, when I run the queries in two passes, it comes back pretty quickly, nowhere near the 45 seconds it takes for the first compound query. Is this something that is unique to SQLITE? Or would any database engine choke on these sorts of queries? Would this go faster if I create an index on 'name'? I believe that the version of SQLITE that I am running is 3.1.3 (I am not on the machine that I was running this on). Is there something out there (on the net) that I should read that explains these sorts of things? Thanks, Mike ___ 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] Question on Queries
Mike McGonagle wrote: > Oh, I forgot to mention (if it matters), the "MAIN" table has about 3000 > rows in it, while the "LIST" table has about 6 rows. > Mike > > > On Mon, Mar 3, 2008 at 3:32 PM, Mike McGonagle <[EMAIL PROTECTED]> wrote: > >> Hello all, >> I was working with some queries last night, and ran accross something that >> I don't quite understand. Basically, this is what I have... >> >> *** >> >> CREATE TABLE MAIN ( >> id integer primary key autoincrement not null, >> name varchar(30), >> [other fields left out, as they are not used] >> ); >> >> CREATE TABLE LIST ( >> mid integer, >> ord integer, >> data float >> ); >> >> -- Compound Query >> SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = >> "something") ORDER BY ord; >> >> -- Individual Queries >> SELECT id FROM MAIN WHERE name = "something"; >> SELECT data FROM LIST WHERE mid = id_as_returned_above; This just screams inner join. SELECT data FROM LIST l INNER JOIN MAIN m ON l.mid = m.id WHERE m.name = "something"; My advice is ALWAYS to avoid subselects unless you ABSOLUTELY have to use them. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Queries
Oh, I forgot to mention (if it matters), the "MAIN" table has about 3000 rows in it, while the "LIST" table has about 6 rows. Mike On Mon, Mar 3, 2008 at 3:32 PM, Mike McGonagle <[EMAIL PROTECTED]> wrote: > Hello all, > I was working with some queries last night, and ran accross something that > I don't quite understand. Basically, this is what I have... > > *** > > CREATE TABLE MAIN ( > id integer primary key autoincrement not null, > name varchar(30), > [other fields left out, as they are not used] > ); > > CREATE TABLE LIST ( > mid integer, > ord integer, > data float > ); > > -- Compound Query > SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = > "something") ORDER BY ord; > > -- Individual Queries > SELECT id FROM MAIN WHERE name = "something"; > SELECT data FROM LIST WHERE mid = id_as_returned_above; > > *** > > So, what is happening is when I run the first query, it takes about 45 > seconds for the data to be returned. It is correct and everything, just > takes a long time. > > But, when I run the queries in two passes, it comes back pretty quickly, > nowhere near the 45 seconds it takes for the first compound query. > > Is this something that is unique to SQLITE? Or would any database engine > choke on these sorts of queries? Would this go faster if I create an index > on 'name'? > > I believe that the version of SQLITE that I am running is 3.1.3 (I am not > on the machine that I was running this on). Is there something out there (on > the net) that I should read that explains these sorts of things? > > Thanks, > > Mike > > -- Peace may sound simple—one beautiful word— but it requires everything we have, every quality, every strength, every dream, every high ideal. —Yehudi Menuhin (1916–1999), musician ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on Queries
Hello all, I was working with some queries last night, and ran accross something that I don't quite understand. Basically, this is what I have... *** CREATE TABLE MAIN ( id integer primary key autoincrement not null, name varchar(30), [other fields left out, as they are not used] ); CREATE TABLE LIST ( mid integer, ord integer, data float ); -- Compound Query SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name = "something") ORDER BY ord; -- Individual Queries SELECT id FROM MAIN WHERE name = "something"; SELECT data FROM LIST WHERE mid = id_as_returned_above; *** So, what is happening is when I run the first query, it takes about 45 seconds for the data to be returned. It is correct and everything, just takes a long time. But, when I run the queries in two passes, it comes back pretty quickly, nowhere near the 45 seconds it takes for the first compound query. Is this something that is unique to SQLITE? Or would any database engine choke on these sorts of queries? Would this go faster if I create an index on 'name'? I believe that the version of SQLITE that I am running is 3.1.3 (I am not on the machine that I was running this on). Is there something out there (on the net) that I should read that explains these sorts of things? Thanks, Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
