On 12/12/15, Cecil Westerhof <cldwesterhof at gmail.com> wrote:
> I am playing with SQLite. I am thinking about writing an application for
> projects. At the moment I have the following:
>
> CREATE TABLE `projects` (
> `projectID` TEXT PRIMARY KEY,
> `groupID` TEXT,
> `isPersonal` INTEGER NOT NULL CHECK(isPersonal in (0, 1)),
> `name` TEXT,
> `description` TEXT,
> `outcome` TEXT
> );
> CREATE INDEX projects_groupID_idx
> ON projects(groupID);
> CREATE INDEX projects_isPersonal_idx
> ON projects(isPersonal);
>
> ?I like to differentiate between personal and non personal projects. Is it
> a good idea to put a index on isPersonal?
No, usually not. An exception would be if the boolean is almost
always true or almost always false. Then using a partial index on
(the infrequent value of) that boolean might make sense.
CREATE TABLE t1(
id INTEGER PRIMARY KEY,
isPersonal BOOLEAN -- usually false
);
CREATE INDEX t1x ON t1(isPersonal) WHERE isPersonal;
Then queries of the form:
SELECT * FROM t1 WHERE isPersonal;
Would use the index, but the index will not take up much space.
>
>
> By the way: I am thinking about using UUID for projectID and groupID, but I
> heard somewhere that it was a bad idea to use UUID for an indexed field. Is
> this true??
>
I think you might have misunderstood. UUID is almost always a good
field to index.
One other point: The use of grave accents to quote column names is a
mysql-ism. SQLite also supports that for compatibility. But you
still shouldn't do it. The proper SQL-standard way is double-quote.
--
D. Richard Hipp
drh at sqlite.org
