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 <sqlite-users@sqlite.org>
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
