Re: [sqlite] Left join help.

On Fri, 26 Apr 2013 14:06:23 +0200 Clemens Ladisch wrote: > When using CROSS where standard SQL allows it (for an actual cartesian > product), the table ordering does not really matter for optimization > purposes because the DB has to do two nested full table scans anyway.

Re: [sqlite] Left join help.

Many thanks to you all, I really appraciate your helpfulness, Danilo 2013/4/26 Clemens Ladisch > Hick Gunter wrote: > > Actually "CROSS" just forces SQLite to use the tables in the order > > specified. > > This is an SQLite optimization extension. I used CROSS because this

Re: [sqlite] Left join help.

Hick Gunter wrote: > Actually "CROSS" just forces SQLite to use the tables in the order > specified. This is an SQLite optimization extension. I used CROSS because this is the only explicit join type where standard SQL allows to omit the join expression. When using CROSS where standard SQL

Re: [sqlite] Left join help.

2 SEARCH TABLE appx USING INDEX sqlite_autoindex_appx_1 (app_ref_itx=? AND app_ref_sub=?) (~1 rows) -Ursprüngliche Nachricht- Von: Clemens Ladisch [mailto:clem...@ladisch.de] Gesendet: Freitag, 26. April 2013 12:40 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Left jo

Re: [sqlite] Left join help.

Don't forget to add an ORDER BY clause if you want a specific order. -Ursprüngliche Nachricht- Von: Danilo Cicerone [mailto:cyds...@gmail.com] Gesendet: Freitag, 26. April 2013 11:40 An: SQLITE Forum Betreff: [sqlite] Left join help. Hi to all, I'm looking for a query on the f

Re: [sqlite] Left join help.

Danilo Cicerone wrote: > table appx stores item's(table itx) quantity load for each user (table > subj). I'd to know how many items each user has: > > Paul|Box|3 > Paul|Letter|0 > Paul|Pen|0 > John|Box|0 > John|Letter|4 > John|Pen|0 > > I tried: > > select sub_descr, itx_descr, app_load from subj

[sqlite] Left join help.

Hi to all, I'm looking for a query on the following schema: PRAGMA foreign_keys=ON; BEGIN TRANSACTION; CREATE TABLE subj ( sub_id INTEGER PRIMARY KEY, -- 00 sub_descr TEXT DEFAULT NULL -- 01 ); INSERT INTO "subj" VALUES(1,'Paul'); INSERT INTO "subj" VALUES(2,'John'); CREATE TABLE itx (

Re: [sqlite] Left Join help

- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Wednesday, April 09, 2008 6:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Left Join help Andy Smith wrote: > > With all the changes that got me down to 4.8 seconds. Better. Can yo

Re: [sqlite] Left Join help

Andy Smith wrote: > > With all the changes that got me down to 4.8 seconds. Better. Can you post the exact query that ran in 4.8 seconds? Did you set case_sensitive_like on before you ran the query? > We are > also trying a schema in which we break up the container so it's a bit > smaller.

Re: [sqlite] Left Join help

ATE INDEX extension_keyvalue ON extension(keyvalue); CREATE INDEX extension_mediaitemID ON extension(mediaitemID); CREATE INDEX mediaAudio_artistID ON mediaAudio(artistID); CREATE INDEX mediaAudio_avgRating ON mediaAudio(avgRating); CREATE INDEX mediaAudio_containerID ON mediaAu

Re: [sqlite] Left Join help

Andy Smith wrote: > I have to get this down to < 1 sec. I have 50 queries that are all > formatted similar. We have created another schema where the container > is split into smaller tables which might help in speeding this up. From > what I see the Left joins are killing the speed. > I don't

Re: [sqlite] Left Join help

pe); CREATE INDEX extend_contact ON extend(contact); CREATE INDEX extend_titleON extend(title); CREATE INDEX extension_containerID ON extension(containerID); CREATE INDEX extension_extendID ON extension(extendID); CREATE INDEX extension_keyvalue ON extension(keyvalue); C

Re: [sqlite] Left Join help

ON container(type); CREATE INDEX extend_contact ON extend(contact); CREATE INDEX extend_titleON extend(title); CREATE INDEX extension_containerID ON extension(containerID); CREATE INDEX extension_extendID ON extension(extendID); CREATE INDEX extension_keyvalue ON extension(k

Re: [sqlite] Left Join help

Andy Smith wrote: > I have quiet a few queries similar to this doing multiple Left Joins and > they run extremely slow > 6 secs. Is there a better way to be writing > the below query for sqlite. > Andy, You didn't show your table and index definitions, so it's hard to be sure about what would

Re: [sqlite] Left Join help

Andy Smith <[EMAIL PROTECTED]> wrote: > I have quiet a few queries similar to this doing multiple Left Joins > and > they run extremely slow > 6 secs. Is there a better way to be writing > the below query for sqlite. > > > > SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, >

[sqlite] Left Join help

I have quiet a few queries similar to this doing multiple Left Joins and they run extremely slow > 6 secs. Is there a better way to be writing the below query for sqlite. SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, a.containerID, a.mimeType, a.width, a.height, a.genreID,