[sqlite] compile sqlite with Visual Studio 6
How can I compile sqlite with Visual Studio 6 with an equivalent option of /fp:precise? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3IsNaN with msvc6
MSVC6 doesn't have /fp: option so this piece of code in main.c always
yields an assert:
if ( rc==SQLITE_OK ){
u64 x = (((u64)1)<<63)-1;
double y;
assert(sizeof(x)==8);
assert(sizeof(x)==sizeof(y));
memcpy(&y, &x, 8);
assert( sqlite3IsNaN(y) );
}
How can I compile sqlite in order to "pass" this assert too?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] check constraint error message
Is it possible to have a custom check constraint error message? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] join performance query
Sorry but in your solution, how can I solve the condition AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke >> version)' OR number > 258) ? title is on song and number is song_number on Playlist_Song AS PS. Furthermore I also need title and number in place of your select * from SONG Could you write it again please? Thanks Citando Tim Romano : > 1. Try discrete single-column indexes rather than multi-column composite > indexes. > 2. Try breaking the query down into subsets expressed as parenthetical > queries; you can treat these parenthetical queries as if they were tables by > assigning them an alias, and then you can join against the aliases. I have > sped queries up in SQLite using this approach and, with a little tinkering, > the time can drop from over a minute to sub-second. Performance will > depend on the indexes and criteria used, of course. But this approach lets > you see how SQLite is optimizing the creation of the component sets from > which you can build up your ultimate query. > . > select * from SONG > JOIN > > ( select id_song from > > ( > select id_song from PLAYLIST_SONG > where id_playlist=2 > ) as MYPLAYLISTSONGS > > JOIN > > ( > select id_song from > SONG > where genre_id = 0 AND artist = 'Las ketchup' > AND title >= 'Asereje(karaoke version)' > ) as MYSONGS > > on MYSONGS.id_song = MYPLAYLISTSONGS.id_song > > > ) as SONGIDLIST > > on SONG.id_song = SONGIDLIST.id_song > > > Regards > Tim Romano > > > > > > > On Tue, May 11, 2010 at 6:07 AM, Andrea Galeazzi wrote: > >> Hi guys, >> I'm in a bind for a huge time consuming query! >> I made the following database schema: >> >> CREATE TABLE Song ( >>idINTEGER NOT NULL UNIQUE, >>titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, >>artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, >>genre_idINT NOT NULL DEFAULT 0, >> PRIMARY KEY (id), >> >> CONSTRAINT fk_Genre FOREIGN KEY (genre_id) >>REFERENCES Genre (id) >>ON DELETE SET DEFAULT >>ON UPDATE CASCADE); >> >> CREATE INDEX Song_Browse_View_idx ON Song(genre_id,artist,title); >> >> CREATE TABLE PlayList ( >>id INTEGER NOT NULL UNIQUE, >>name VARCHAR(15) NOT NULL COLLATE NOCASE, --KORGCOLLATE, >>length INT NOT NULL DEFAULT 0, >>created_date TEXT, >> PRIMARY KEY (id)); >> >> CREATE TABLE PlayList_Song ( >>id_song INT NOT NULL, >>id_playlist INT NOT NULL, >>song_number INTEGER NOT NULL, >> PRIMARY KEY (id_playlist, song_number), >> CONSTRAINT fk_PlayList_Song1 FOREIGN KEY (id_song) >>REFERENCES Song (id) >>ON DELETE CASCADE >>ON UPDATE CASCADE, >> CONSTRAINT fk_PlayList_Song2 FOREIGN KEY (id_playlist) >>REFERENCES PlayList (id) >>ON DELETE CASCADE >>ON UPDATE CASCADE); >> >> CREATE INDEX PlayList_Song_song_number_idx ON PlayList_Song(song_number); >> >> Now I need to scroll title filtered by genre_id and artist both in Song >> table and Playlist. >> The query for the first case is very fast: >> SELECT id AS number,title FROM Song WHERE genre_id = 0 AND artist = >> 'Las ketchup' >> AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke >> version)' OR number > 258) >> ORDER BY title ASC , number ASC LIMIT 4; >> >> The second case is about 35 times slower... so the scrolling is quite >> impossible (or useless)! >> SELECT song_number AS number,title FROM Song AS S, Playlist_Song AS PS >> WHERE S.id = PS.id_song AND >> PS.id_playlist = 2 AND genre_id = 0 AND artist = 'Las ketchup' >> AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke >> version)' OR number > 959) >> ORDER BY title ASC , number ASC LIMIT 4; >> >> I also execute the EXPLAIN QUERY PLAN: >> 1st query: 0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY >> >> 2nd query: 0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY >> 1 1 TABLE Playlist_Song AS PS >> So it seems that the second plan (1,1) requires very long time! >> How can I optimized a such kind of query? >> Cheers >> >> ___ >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] join performance query
Hi guys, I'm in a bind for a huge time consuming query! I made the following database schema: CREATE TABLE Song ( idINTEGER NOT NULL UNIQUE, titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, genre_idINT NOT NULL DEFAULT 0, PRIMARY KEY (id), CONSTRAINT fk_Genre FOREIGN KEY (genre_id) REFERENCES Genre (id) ON DELETE SET DEFAULT ON UPDATE CASCADE); CREATE INDEX Song_Browse_View_idx ON Song(genre_id,artist,title); CREATE TABLE PlayList ( id INTEGER NOT NULL UNIQUE, name VARCHAR(15) NOT NULL COLLATE NOCASE, --KORGCOLLATE, length INT NOT NULL DEFAULT 0, created_date TEXT, PRIMARY KEY (id)); CREATE TABLE PlayList_Song ( id_song INT NOT NULL, id_playlist INT NOT NULL, song_number INTEGER NOT NULL, PRIMARY KEY (id_playlist, song_number), CONSTRAINT fk_PlayList_Song1 FOREIGN KEY (id_song) REFERENCES Song (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_PlayList_Song2 FOREIGN KEY (id_playlist) REFERENCES PlayList (id) ON DELETE CASCADE ON UPDATE CASCADE); CREATE INDEX PlayList_Song_song_number_idx ON PlayList_Song(song_number); Now I need to scroll title filtered by genre_id and artist both in Song table and Playlist. The query for the first case is very fast: SELECT id AS number,title FROM Song WHERE genre_id = 0 AND artist = 'Las ketchup' AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke version)' OR number > 258) ORDER BY title ASC , number ASC LIMIT 4; The second case is about 35 times slower... so the scrolling is quite impossible (or useless)! SELECT song_number AS number,title FROM Song AS S, Playlist_Song AS PS WHERE S.id = PS.id_song AND PS.id_playlist = 2 AND genre_id = 0 AND artist = 'Las ketchup' AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke version)' OR number > 959) ORDER BY title ASC , number ASC LIMIT 4; I also execute the EXPLAIN QUERY PLAN: 1st query: 0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY 2nd query: 0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY 1 1 TABLE Playlist_Song AS PS So it seems that the second plan (1,1) requires very long time! How can I optimized a such kind of query? Cheers ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] find same type
I've got this table TABLE T ( idINTEGER NOT NULL UNIQUE, file_typeVARCHAR(10) NOT NULL) My goal is to check if a certain selection has all the same values. I thought that the following statement should be enough for my aim: SELECT (SELECT file_type FROM T T1, T T2 WHERE T1.id IN (1,4,5) AND T2.id IN (1,4,5) AND T1.file_type <> T2.file_type LIMIT 1) IS NULL Does anyone know a fastest query to achieve that? Cheers. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expression tree is too large
Thanks, it works! Frank Baumgart ha scritto: >> - Ursprüngliche Nachricht - >> Von: Andrea Galeazzi >> Gesendet: 05.05.10 12:14 Uhr >> An: General Discussion of SQLite Database >> Betreff: [sqlite] Expression tree is too large >> >> > Hi guys, > I've got a DELETE statement with a lot of OR: > DELETE FROM myTable WHERE id = ? OR id = ?..OR id=? > > > > -> ... WHERE id IN (?, ?, ...) > > Frank > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ Informazioni da ESET NOD32 Antivirus, versione del database delle > firme digitali 5087 (20100505) __ > > Il messaggio è stato controllato da ESET NOD32 Antivirus. > > www.nod32.it > > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Expression tree is too large
Hi guys, I've got a DELETE statement with a lot of OR: DELETE FROM myTable WHERE id = ? OR id = ?..OR id=? and SQLite throws this error: Expression tree is too large. Do you know a way to avoid such problem or I just have to split the large statement into shorter ones? Cheers ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] changing check constraints
Is it possible to change check constraints? Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] if exist
It works fine! Thanks! Martin.Engelschalk ha scritto: > Hi, > > try this: > > select coalesce(min(length), 0) from t where id = ? > > Martin > > Andrea Galeazzi schrieb: > >> Hi All, >> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and >> INT length. >> I need a statement in order to yield 0 when the key doesn't exist. At >> this moment the query is too simple: >> SELECT length FROM T WHERE id = ? >> Any idea about it? >> Cheers >> ___ >> 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 > > __ Informazioni da ESET NOD32 Antivirus, versione del database delle > firme digitali 4927 (20100309) __ > > Il messaggio è stato controllato da ESET NOD32 Antivirus. > > www.nod32.it > > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] if exist
Hi All, I've got a table T made up of only two fields: INT id (PRIMARY KEY) and INT length. I need a statement in order to yield 0 when the key doesn't exist. At this moment the query is too simple: SELECT length FROM T WHERE id = ? Any idea about it? Cheers ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE the order of INT fileds
Simon Slavin ha scritto: > On 22 Dec 2009, at 10:25am, Andrea Galeazzi wrote: > > >> ID INTEGER, >> Name TEXT >> >> So, for instance, I can have: >> >> 1 Julia >> 2 Eric >> 3 Kevin >> 4 Sarah >> 5 John >> >> Now I wanna move Eric from 2 to 4 in order to yield (by performing a >> series of UPDATE of ID field): >> >> 1 Julia >> 2 Kevin >> 3 Sarah >> 4 Eric >> 5 John >> >> What's the correct sequence of SQL statements to accomplish a such kind >> of task? >> > > Something like > > UPDATE myTable SET id=id-1 WHERE id>oldplace AND id<=newplace > UPDATE myTable SET id=newplace WHERE name='Eric' > > You will need to detect whether you're moving the single record up or down > and change the UPDATE accordingly (or you could use ABS but that would be > even more confusing). > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ Informazioni da ESET NOD32 Antivirus, versione del database delle > firme digitali 4708 (20091222) __ > > Il messaggio è stato controllato da ESET NOD32 Antivirus. > > www.nod32.it > > > > > Probably it won't work because ID must be UNIQUE and Name is not. Other ideas? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE the order of INT fileds
Hi, I'm trying to solve this problem: I've got the following table, made up of two fields: ID INTEGER, Name TEXT So, for instance, I can have: 1 Julia 2 Eric 3 Kevin 4 Sarah 5 John Now I wanna move Eric from 2 to 4 in order to yield (by performing a series of UPDATE of ID field): 1 Julia 2 Kevin 3 Sarah 4 Eric 5 John What's the correct sequence of SQL statements to accomplish a such kind of task? Obviously I could also want to move the records in the opposite order (3 to 1) Cheers ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] any keyword
Probably sqlite doesn't support 'any' keyword as I write it in the following query: SELECT G.id,name FROM Genre G WHERE G.id = ANY (SELECT S.genre_id FROM Song S) ORDER BY name ASC; In this case I can write an equivalent query like: select G.id,name from Genre G WHERE (SELECT COUNT(*) FROM Song S WHERE G.id = S.genre_id) > 0 ORDER BY name; Anyway, could I avoid to use count which require a very long time? Does the development team have a plan including the 'any/all' keyword implementation? I think it should be useful for many users. Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_CORRUPT error
So I'm gonna find this fix in the 3.6.16 version right? I'd like to avoid to download it directly from cvs... D. Richard Hipp ha scritto: > On Jun 23, 2009, at 3:16 AM, Andrea Galeazzi wrote: > > >> Any news about this problem? >> > > http://www.sqlite.org/cvstrac/tktview?tn=3929 > > >> galea...@korg.it ha scritto: >> >> In order to be more confidence about what I'm saying, I downloaded the >> precompiled sqlite console 3.6.15 (windows version), I executed the >> statement above and I've got the following error: >> >> sqlite3.exe malformed_db.db >> SQLite version 3.6.15 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sqlite> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) >> VALUES >>...> (5235, 9, 256); >> SQL error: database disk image is malformed >> sqlite> .q >> >> Then I tried with a previous version and the statement has been well >> executed. A colleague of mine made the same test and he had the same >> troubles. So I don't thing it's a compiler issue. >> Did you make the test with windows console? Have I send you the >> database again? >> Thanks for your helpfulness! >> >> >> >> >>> Citando "D. Richard Hipp" : >>> >>> >>> >>>> On Jun 18, 2009, at 12:36 PM, galea...@korg.it wrote: >>>> >>>> >>>> >>>>> This statement is giving me truoble: >>>>> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES >>>>> (5235, 9, 256) >>>>> >>>>> >>>> That INSERT statement works fine for me. >>>> >>>> Did you try recompiling with optimizations turned off? >>>> >>>> >>>> >>>> D. Richard Hipp >>>> d...@hwaci.com >>>> >>>> >>>> >>>> ___ >>>> 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 >>> >>> __ Informazioni da ESET NOD32 Antivirus, versione del >>> database delle firme digitali 4177 (20090622) __ >>> >>> Il messaggio è stato controllato da ESET NOD32 Antivirus. >>> >>> www.nod32.it >>> >>> >>> >>> >>> >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ Informazioni da ESET NOD32 Antivirus, versione del database delle > firme digitali 4182 (20090624) __ > > Il messaggio è stato controllato da ESET NOD32 Antivirus. > > www.nod32.it > > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_CORRUPT error
Any news about this problem? galea...@korg.it ha scritto: In order to be more confidence about what I'm saying, I downloaded the precompiled sqlite console 3.6.15 (windows version), I executed the statement above and I've got the following error: sqlite3.exe malformed_db.db SQLite version 3.6.15 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES ...> (5235, 9, 256); SQL error: database disk image is malformed sqlite> .q Then I tried with a previous version and the statement has been well executed. A colleague of mine made the same test and he had the same troubles. So I don't thing it's a compiler issue. Did you make the test with windows console? Have I send you the database again? Thanks for your helpfulness! > Citando "D. Richard Hipp" : > > >> On Jun 18, 2009, at 12:36 PM, galea...@korg.it wrote: >> >> >>> This statement is giving me truoble: >>> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES >>> (5235, 9, 256) >>> >> That INSERT statement works fine for me. >> >> Did you try recompiling with optimizations turned off? >> >> >> >> D. Richard Hipp >> d...@hwaci.com >> >> >> >> ___ >> 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 > > __ Informazioni da ESET NOD32 Antivirus, versione del database delle > firme digitali 4177 (20090622) __ > > Il messaggio è stato controllato da ESET NOD32 Antivirus. > > www.nod32.it > > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select performance with join
Hi guys, I've got a big problem about select performance on an left join. I have two tables: CREATE TABLE Song ( id INTEGER NOT NULL UNIQUE, title VARCHAR(40) NULL COLLATE NOCASE, artist VARCHAR(40) NULL COLLATE NOCASE, bpm INT NULL, genre_id INT NULL, PRIMARY KEY (id), CONSTRAINT fk_Genre FOREIGN KEY (genre_id) REFERENCES Song (id) ON DELETE SET NULL ON UPDATE CASCADE); -- Indeces CREATE INDEX Song_title_idx ON Song(title); CREATE INDEX Song_artist_idx ON Song(artist); CREATE INDEX Song_bpm_idx ON Song(bpm); CREATE INDEX Song_genre_idx ON Song(genre_id); CREATE TABLE Genre ( id INTEGER NOT NULL UNIQUE, name VARCHAR(20) NOT NULL COLLATE NOCASE, image_id INT NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_Image2 FOREIGN KEY (image_id) REFERENCES Genre (id) ON DELETE SET NULL ON UPDATE CASCADE); -- Indeces CREATE INDEX Genre_name_idx ON Genre(name); - Now when I execute this query: SELECT S.id,title,artist,bpm,name FROM Song AS S LEFT JOIN Genre AS G ON (S.genre_id = G.id) WHERE title<= 'zzX_5238' AND (title< 'zzX_5238' OR S.id< 5238) ORDER BY title DESC, S.id DESC LIMIT 20; it takes only 200 ms but when I execute: SELECT S.id,title,artist,bpm,name FROM Song AS S LEFT JOIN Genre AS G ON (S.genre_id = G.id) WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122) ORDER BY name DESC, S.id DESC LIMIT 20; it takes 8100! It's a huge time for our application! I also noticed that the Genre_name_idx it's useless. Song has 1 records, Genre has 100 records. Does anyone have any ideas about how to improve the previous query? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index optimization
I meant: SELECT title FROM tracks WHERE title >= :last_title AND (title>:last_title OR id>:last_id) AND title LIKE %Mad% ORDER BY title,id; better than SELECT title FROM tracks WHERE title LIKE %Mad% AND title >= :last_title AND (title>:last_title OR id>:last_id) ORDER BY title,id; ? Furthermore can the index improve select performance in both previous cases? Igor Tandetnik ha scritto: > "Andrea Galeazzi" wrote in > message news:49bf6196.5070...@korg.it > >> Is the order of WHERE clauses important as C-language is? >> > > No. The optimizer may, and often does, reorder the conditions in the > WHERE clause. > > >> For instance is >> >> SELECT title FROM tracks >> WHERE title ((title=:last_title AND id>:last_id) AND LIKE %Mad% >> >> better than >> >> SELECT title FROM tracks >> WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) >> >> ? >> > > Neither is better than the other: both are invalid and will produce a > syntax error. > > >> Anyway, can the index improve select performance in both previous >> cases? >> > > No. These statements won't actually run, and are thus beyond help. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ NOD32 3941 (20090317) Information __ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index optimization
> > Is the order of WHERE clauses important as C-language is? > For instance is > > SELECT title FROM tracks >WHERE ((title=:last_title AND id>:last_id) AND title LIKE %Mad% > > better than > > SELECT title FROM tracks >WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) > > ?Anyway, can the index improve select performance in both previous cases? > > Igor Tandetnik ha scritto: > >> "Andrea Galeazzi" wrote in >> message news:49be74fd.6060...@korg.it >> >> >>> I red this article on wiki: >>> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor >>> I've got a similar case but the difference is that I've to use LIKE >>> operator instead of = >>> SELECT title FROM tracks >>>WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) >>> OR ((title>:last_title)) ORDER BY title,id; >>> id is the primary key and I created an index for (id,title). >>> My question is: will the previous query be actually faster then just >>> only using OFFSET and LIMITS even if I also need a LIKE operator on >>> title column? >>> >>> >> SQLite won't be able to use the index to satisfy LIKE condition. So you >> should concentrate on the other clauses. An index on (title, id) should >> help. For greater effect, change the query to >> >> SELECT title FROM tracks >> WHERE title LIKE %Mad% AND >> title >= :last_title AND >> (title>:last_title OR id>:last_id) >> ORDER BY title,id; >> >> Igor Tandetnik >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> __ NOD32 3939 (20090316) Information __ >> >> This message was checked by NOD32 antivirus system. >> http://www.eset.com >> >> >> >> >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ NOD32 3941 (20090317) Information __ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index optimization
Is the order of WHERE clauses important as C-language is? For instance is SELECT title FROM tracks WHERE title ((title=:last_title AND id>:last_id) AND LIKE %Mad% better than SELECT title FROM tracks WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) ? Anyway, can the index improve select performance in both previous cases? Igor Tandetnik ha scritto: > "Andrea Galeazzi" wrote in > message news:49be74fd.6060...@korg.it > >> I red this article on wiki: >> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor >> I've got a similar case but the difference is that I've to use LIKE >> operator instead of = >> SELECT title FROM tracks >>WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) >> OR ((title>:last_title)) ORDER BY title,id; >> id is the primary key and I created an index for (id,title). >> My question is: will the previous query be actually faster then just >> only using OFFSET and LIMITS even if I also need a LIKE operator on >> title column? >> > > SQLite won't be able to use the index to satisfy LIKE condition. So you > should concentrate on the other clauses. An index on (title, id) should > help. For greater effect, change the query to > > SELECT title FROM tracks > WHERE title LIKE %Mad% AND > title >= :last_title AND > (title>:last_title OR id>:last_id) > ORDER BY title,id; > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ NOD32 3939 (20090316) Information __ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index optimization
Andrea Galeazzi ha scritto: > I red this article on wiki: > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > I've got a similar case but the difference is that I've to use LIKE > operator instead of = > SELECT title FROM tracks > WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) > OR ((title>:last_title)) ORDER BY title,id; > id is the primary key and I created an index for (id,title). > My question is: will the previous query be actually faster then just > only using OFFSET and LIMITS even if I also need a LIKE operator on > title column? > Thanks. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ NOD32 3939 (20090316) Information __ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > > ERRATA CORRIGE ...then -> than ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index optimization
I red this article on wiki: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor I've got a similar case but the difference is that I've to use LIKE operator instead of = SELECT title FROM tracks WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) OR ((title>:last_title)) ORDER BY title,id; id is the primary key and I created an index for (id,title). My question is: will the previous query be actually faster then just only using OFFSET and LIMITS even if I also need a LIKE operator on title column? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] nullable select fields
Hi All, I'm developing an application which relies on sqllite as back-end. Now I face to this problem: I've got a form that allows the user to fill a lot of fields, obliviously only a little part of them will actually be filled, the others isn't gonna be in the search criteria. So I prepare a parameterized query containing the whole possible fields like this: SELECT * FROM Song WHERE id = ? AND title = ? AND album LIKE '%?%'; How can I bind the unrequested fields? Does a trivial solution exist? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
