[sqlite] Is it faster with descending index

[Ertan Küçükoğlu]

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 
Subject: Re: [sqlite] Is it faster with descending index

On 5/5/16, Ertan K???ko?lu  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

[sqlite] Is it faster with descending index

[Ertan Küçükoğlu]

Hi,

I am using SQLite version 3.9.2 on WinCE devices. I have below table in one
of my databases. Table sometimes holds 1.5 to 3 million records in it. That
database is reached for read-only purposes and created from scratch in a PC
if its contents needs to be updated. All database files, application, and
some application related files are on a microSD card. Brand and size is
changing from device to device.

Database opened using below pragmas:
PRAGMA page_size=4096;
PRAGMA journal_mode=MEMORY;
PRAGMA temp_store=2; // Memory temp store
PRAGMA locking_mode=EXCLUSIVE;

Table create 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),
 SeriNo Char(1),
 Lot Char(1),
 SKT Char(1)
);

My question is, if I add following index, will it make my searches faster?

CREATE UNIQUE INDEX IDX_BARKOD_DESC ON URUN(BARKOD);

Application is developed using Lazarus 1.6. Table is opened in a TSQLQuery
and searches coded using "TSQLQuery.Locate('Barkod', Search_Value, []);"

Thanks & Regards,
Ertan K???ko?lu

[sqlite] Is it faster with descending index

[Richard Hipp]

On 5/5/16, Ertan K???ko?lu  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