I prepared a test application on Windows. Used sqlite 3.12.2 32bit DLL. - Just one table in a database. - Database1, Table created with below SQL and second database I included WITHOUT ROWID at the end when creating the test table. - Application filled in 500.000 random value generated records in first database1. - Application generated exactly field size random value strings for each field separately. (Char(30) field, value inserted with 30 characters, Char(60) field, value inserted with 60 characters and so on) - Application generated strings between Chr(32) - Chr(90) in ASCII table, so nothing non-printable inserted. - Application select all rows from database1 & inserted to database2. - Test showed me having insert values ready is faster than generating insert values.
As a result database2 size is about 5.2% smaller. I do not know how faster queries will be in that small database but still a gain. Table SQL: CREATE TABLE URUN( Barkod Char(30) NOT NULL PRIMARY KEY, UrunKodu Char(50), UrunAciklamasi Char(60), UrunGrubu Char(30), Renk Char(20), Beden Char(20), Cup Char(20), OlcuBirimi Char(10), AlisFiyat Char(20), SatisFiyat Char(20) ); Thanks. Ertan K???ko?lu -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, May 6, 2016 12:51 AM To: SQLite mailing list <sqlite-users at mailinglists.sqlite.org> Subject: Re: [sqlite] Is it faster with descending index On 5/5/16, Ertan K???ko?lu <ertan.kucukoglu at 1nar.com.tr> wrote: > > Table create SQL: > CREATE TABLE URUN( > Barkod Char(30) NOT NULL PRIMARY KEY, ... > ); > > My question is, if I add following index, will it make my searches faster? > > CREATE UNIQUE INDEX IDX_BARKOD_DESC ON URUN(BARKOD); No. But if you change your table schema to read: CREATE TABLE URUN( Barkod char(30) PRIMARY KEY, .... ) WITHOUT ROWID; Then it might well generate a smaller and faster database. It seems like it is worth a try, anyhow. -- D. Richard Hipp drh at sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
